Transfer SQL Data between two databses

Question

I am using two similar SQL database in two different servers one is local and another one is online. I want to transfer data at the end of the day from the local server to the online server.

what is the best method to automatically transfer data and protecting primary keys effectively.

Thank you

Has your issue been resolved or do you need further assistance with it? — Ron.B.I, Jul 13 '13 at 18:09

score 2 · Answer 1 · answered Jul 12 '13 at 07:09

2

Use Red-Gate Data Compare. It's commercial, though.

(I'm just a satisfied customer and in no way related to Red-gate)

answered Jul 12 '13 at 07:09

okrumnow

2,346
23
39

score 1 · Answer 2 · answered Jul 12 '13 at 07:10

1

Open SQL Server Management Studio and connect to both servers, in the Object Explorer right-click on a server and choose Tasks, and select Import Data or Export Data, then it's a simple wizard to go from there.

SSMS can also do a schema compare (no need to pay for RedGate Comparison software) if needed.

answered Jul 12 '13 at 07:10

Dai

141,631
28
261
374

Thanks Dai, but I want to use it automatically. – Kamran Jul 12 '13 at 07:18
You can use SQL Server Agent to run a Data Import/Export job (using DTS) on a scheduled basis. – Dai Jul 12 '13 at 07:28

score 1 · Answer 3 · edited May 23 '17 at 12:31

I believe what you are trying to do is a Mirror database, updated daily, if that is the case:

Using Database Mirroring is a best practice (Instead of manually doing this yourself), I suggest:

Read about Mirroring here: Database Mirroring
Follow this guide: Setting Up Database Mirroring

Your local server should be the principal and your online will be the mirror

I highly recommend this approach , instead of manually scripting the data (see link to answer below), it will give you benefits such as automatic failover (when your local server crashes it will use the remote one) , you can read all about the benefits in the links above.

If you eventually want to do it manually for any reason, or you don't have the SQL Server Enterprise edition , then read my answer to this question:

sql-server-copying-tables-from-one-database-to-another

database mirroring is only available on SQL server Enterprise edition and developer edition — Yugz, Jul 12 '13 at 07:14

score 1 · Answer 4 · answered Jul 12 '13 at 07:13

1

Have you considered using Replication? replication tutorial

answered Jul 12 '13 at 07:13

Yugz

677
1
10
23

Transfer SQL Data between two databses

4 Answers4