Copy Data From One Instance To Another Instance In SQL Server

Sometimes we need to copy a database from one instance to another instance in SQL Server. There are two ways to achieve this task.

Restoring database backup:

To copy an entire database we can generate backup and restore this backup to another instance. You can read about this method more here:

Restore Database Backup.

Using Copy Database Wizard:

We can use the Copy Database Wizard to copy or move databases between server instance. Let us read how to use this method using the below given simple steps.

Step 1: Right click on any database and select Tasks option and in Tasks select Copy Database.





Step 2: Select specific source server,



Step 3: Select destination server,




If you get the following error that means your SQL Server Agent is not running. For copying the database it is required that SQL Server instance must run. To remove the above error run your SQL Server instance.

For this press “Window+R” and type “services.msc”.



Search your SQL Server instance and run this instance.



Step 4: Select the transfer method. Use this method if you want to keep the database online when it is being copied.



Step 5: Select databases that you want to copy. You can also select the option of move.



Step 6: Create an appropriate database name.



Step 7: Select server object. You can select additional options to copy the data.



Step 8: Configure the package package. Wizard will create a service package with the property that you specified.



Step 9: Schedule the package. You can run service package or you can schedule to run later.



Step 10: Complete the wizard,




Above message shows that data is successfully copied to another SQL Server instance.

Today we learned how to copy data from one SQL Server instance to another SQL Server instance. I hope you liked this article.

Read more articles on SQL Server:

• Basics Of Database Administration In SQL Server: Part 6
• New Features Of SQL Server 2016