SQL SERVER – Copy Database from Instance to Another Instance – Copy Paste in SQL Server

SQL Server has feature which copy database from one database to another database and it can be automated as well using SSIS.

Make sure you have SQL Server Agent Turned on as this feature will create a job. The same job will execute the task. Make sure that SSIS is properly configured as well with necessary security permissions. You can automate this process as well control error logging.

Following are the steps to copy database from one instance to another instance.

Specify Source Server

Specify Destination Server

Here you can select option if you want to keep the database ONLINE when it is being copied.

You can also select option of MOVE or COPY database as well.

Give appropriate database name.

On this screen you can select additional options to copy as well.

You create the package over here.

You can schedule the package using SQL Server Agent.

When this process is over it will show the success message and database will be copied to another server.

You can see how easy is the process to copy the database to another server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)