CODEWITHSUNDEEP
databasesql-server-2008data-transfer
Kamran
Kamran

Reputation: 91

Transfer SQL Data between two databses

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

Upvotes: 0

Views: 1142

Answers (4)

Ron.B.I
Ron.B.I

Reputation: 2766

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:

  1. Read about Mirroring here: Database Mirroring

  2. 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

Upvotes: 1

Yugz
Yugz

Reputation: 677

Have you considered using Replication? replication tutorial

Upvotes: 1

Dai
Dai

Reputation: 155055

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.

Upvotes: 1

okrumnow
okrumnow

Reputation: 2416

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

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

Upvotes: 2

Related Questions