Copy data between 2 databases in different servers

SQL Server Administration
1

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.

2

Create SSIS package and schedule it.

1 Like
3

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

4

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

1 Like
5

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.

6

Transactional replication can help.

1 Like
7

The whole database?
Powershell or python is another option

1 Like
8

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

9

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
10

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

1 Like
11

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

12

What did you try, show us

13

HI,
Once a day => restore
Every hour : logshipping