SQL Server Copy Database Wizard

SQL Server Copy Database Wizard provides a handy SQL Server tool for database administrators and developers to copy sql database from one SQL Server instance to another SQL Server. SQL Server DBA's can copy or move a SQL database and its objects to another SQL Server or within the same SQL Server instance using SQL Server Copy Database Wizard. What is good with Copy Database Task is it prevents the source SQL Server instance from a possible downtime especially if copy database process is configured to use SQL Management Object (SMO) method.

In this SQL Server tutorial, I'll try to demonstrate how to copy database in SQL Server 2008 R2 database instance. To make the step by step tutorial easier and concantrate on SQL Server tips about this excellent sql tool, I will create a copy of a sample sql database within the same instance.

Step by Step Copy Database Wizard Tool for SQL Server

For either SQL Server 2005 Copy Database task, SQL Server 2008 Copy Database or move database tasks, the sql wizard is nearly the same and database administrators can use the following steps as a guide during their daily works.

In order to start SQL Server Copy Database Wizard, connect to SQL Server Management Studio (SSMS).
Connect to a SQL Server instance, preferrably to the SQL Server instance where your source database is in.
Within the Object Explorer window, drill through Databases node. Right click on the source database and follow the following menu options.
Tasks > Copy Database...

When the SQL Server Copy Database task is selected, the splash screen or the welcome screen will be displayed at first.

Database administrators can start configuring copy database tool for their requirements by selecting the source server where they want to move or copy databases from. Besides choosing the source SQL Server instance, the authentication method to source server is provided here as well.
Admins or developers can choose Windows Authentication or SQL Server Authentication (with given sql user name and password).

The next step in copy database SQL Server tool is providing the target SQL Server instance, or destination SQL Server for the sql copy database wizard.
The login priviledges is also provided in this wizard screen just as done in source server configuration screen.

The most important task configuration step in Copy Database SQL Server tool is this screen where DBA's select the transfer method of the source database to destination server.

Use the detach and attach method
This method is faster, but requires the source database to go offline. It is best for upgrading databases or moving very large databases. No user connections to the source database are allowed when using this option.
If a failure occurs, reattach the source database

Use the SQL Management Object method
This method is slower but the source database can remain online.

Although the wizard is activated by selecting a specific database in the Object Explorer Databases node, at this step of this SQL tool, IT professionals can copy or move more than one database by marking the checkbox next to the database name.

Users can mark either Move or Copy checkboxes for database operations

The status field is giving information for the related source database on destination SQL Server instance.
For example, in this SQL Server tutorial the source and destination instances are same. So all databases are marked with "Already exists at destination"
Although the databases are already in the target SQL Server instance, you can still choose Move or Copy database option with a new database name for the destination instance.

Please note that a System database cannot be copied or moved to another SQL Server.

For the sake of simplicity, I chose one of my sample SQL databases SQLSamplesDB to copy with a new name on the same database instance.

If you have choosen more than one source database to copy or move, you have to configure each database selection individually by using this "Configure Destination Database" wizard screen.

The source database name is displayed on readonly "Source database" textbox.
You can keep the database name same or rename the SQL Server database before copy database or move database task is processed.
Since I'm copying sql database within the same SQL Server instance, I have to rename the destination database as seen in the below screenshot.

The database files and the database file locations are configured with default values for the user. But SQL admins or developers can modify data files and log files names or place of those files by choosing a different folder.

One last configuration setting for destination database is the behaviour you'll prefer in case the destination database already exists on the target SQL Server instance.

If you plan to schedule Copy Database task and execute copy task periodically to create a copy of your transactional database for reporting purposes, etc you can choose to overwrite existing database. In this case, you can select option :
Drop any database on the destination server with the same name, then continue with the database transfer, overwriting existing database files.

But if you don't want to delete accidentally a database with the same name that exists on the destination server, then you can choose the option:
Stop the transfer if a database or file with the same name exists at the destination.

If you want to use this task only once and execute sql Copy Database wizard immediately, you can select option "Run immediately" and proceed to next step.

But if you want to use Copy Database task with current configuration option periodically, you can create a schedule.
For this option, select "Schedule" and press "Change schedule" buton to define the best schedule that fits to your requirement.

The SQL Server tool Copy Database task creates an Integration Services package at the end.
At this SQL wizard, admins can define SSIS package properties like package name, logging options and error log file path, etc.

Here is a last review chance for you to check the SSIS package settings for the Copy Database SQL Server tool task.

When you press Finish buton, the task wizard will start executing the Copy Database or Move Database process. In this screen, you can view the status of each execution step in SQL Server Copy Database tool.

If you wonder where the SSIS package is saved, you can connect to local SQL Server Integration Services instance within SSMS (SQL Server Management Studio). The SSIS package created by Copy Database SQL Server tool is stored in Stored Packages > MSDB > "instance name" > DTS Packages > Copy Database Wizard Packages

One last note for SQL Server database administrators, SQL Server teams are releasing new tools for SQL Server with each new version and even with new service pack releases. So keep on looking for new applications and new options within the SQL Server Management Studio which will help you manage your daily tasks easier and faster. While the days of SQL Server 2011, Denali release date is coming closer, I'm waiting for new SQL Server tools within the new database platform from Microsoft.