Copy Data From One Instance To Another Instance In SQL Server

In this article you will learn how to 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.

object

copy

Step 2:
Select specific source server,

server

Step 3: Select destination server,

destination
copy database

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

run

Search your SQL Server instance and run this instance.

service

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

copy database

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

copy database

Step 6: Create an appropriate database name.

name

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

copy database

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

configure

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

schedule

Step 10: Complete the wizard,

wizard
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: