CODEWITHSUNDEEP
sqlsql-serversql-server-2008dts
DotnetSparrow
DotnetSparrow

Reputation: 27996

How can I move data from one SQL Server to other?

I want to copy all my database to remote database server. I have access to server using SQL server management studio.

How can I do this? I have created script with data of old database and tried to run it on new server but it is taking too much time and returns different errors like duplicate key etc.

Upvotes: 1

Views: 458

Answers (3)

Vladimir Tarasov
Vladimir Tarasov

Reputation: 631

Try Database publishing wizard by Microsoft. Good enough for no very big data amounts.

Upvotes: 0

Shan Plourde
Shan Plourde

Reputation: 8706

Assuming that you can restore databases to your remote SQL server, you can use SQL Server backup and restore? They're available in SQL Server Management Studio. See http://msdn.microsoft.com/en-us/library/ms187510.aspx. It's very easy to use.

Other than that, you can try copying the database MDF and LDF files from your local database onto your remote database filesystem, then attaching to the MDF file on your remote database.

Those are probably the fastest ways that you can copy and entire database to a remote location that I can think of.

Upvotes: 1

Robert Beaubien
Robert Beaubien

Reputation: 3156

If this is a one time deal, you should download Redgate's SQL tools. They can handle this easily and are free to test for 14 days I believe. (http://www.red-gate.com) You might even find they are worth the purchase as I have. The tools you would use are SQL Compare (to copy structure) and SQL DAta Compare to actually copy the data.

Upvotes: 2

Related Questions