Search code examples
sqlsql-serversql-server-2008dts

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.

Solution

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