SQLTeam.com | Weblogs | Forums

Copy data between 2 databases in different servers

Hello every one

I need to copy data between 2 different SQL Servers.
I need it to be scheduled every hour. how can I do this?

I tried the Insert into query on SQL Job (for schedule) but there is limitation with the insert into query.
so if there's another method or I can modify the job I would be happy to know

thanks in advance.

Create SSIS package and schedule it.

1 Like

thank ahmeds08 for your reply.
can I do this without SSIS?
we don't want to deploy a SSIS on the servers.

What is your requirement?
Is this for reporting or a DR?

1 Like

I have 2 SQL Servers.
this is not reporting and no DR.
I just need to copy the data from one server to another.
but I want to make it once a day as the source database is updating.

Transactional replication can help.

1 Like

The whole database?
Powershell or python is another option

1 Like

thanks yosiasz
actually I need only one table to copy and it have 2 fields only.
so I'm looking for a simple way.
how to use powershell to schedule copy the database

You mean copy the 2 columns, not the database. Please post the table name and structure in both source and destination servers.

Also what happens to the table on daily basis? Does it get wiped out and then reloaded or leave the existing data and only insert new rows? What happens to rows that already exist in both source and destination server? Do they get updated in destination server if it changes in source server.

1 Like

Another option I totally missed, created linked server, then just do linkedserver.dbname.dbo.customers from target server

1 Like

thanks yosiasz
I tried linkedserver.dbname.dbo.customers but this is not possible for insert into

What did you try, show us

Once a day => restore
Every hour : logshipping