The SQL Server Copy Database Wizard

In this article, we will be discussing about a very useful built-in SQL Server Management Studio tool, that is the SQL Server Copy Database Wizard.

 

About the Copy Database Wizard in SQL Server Management Studio

There is many times where we might need to copy or move a database with the least amount of effort. The “manual” way of performing the above tasks is by using the backup/restore or attach/detach functionality.

SQL Server 2000 introduced the Copy Database Wizard; a feature which allows the DBA with just a few clicks to copy or move an entire database. Though, this version of the Copy Database Wizard allowed copying/moving databases only between different instances of SQL Server 2000 (it also allowed copying/moving databases from SQL Server 7.0 instances to SQL Server 2000).

In more recent versions of SQL Server, like 2005, 2008 and later, the Copy Database Wizard was enhanced with even more functionality. An example is that now you can also copy a database within the same SQL Server instance, thus easily creating a duplicate of it, by using the wizard.

In this post we will go through the process of using the Copy Database Wizard in SQL Server 2008.

 

Let’s See a Usage Example of the Copy Database Wizard

In order to start the wizard (in SQL Server 2005 and 2008), you have to right-click on a database from within SQL Server Management Studio and from Tasks select the Copy Database task (in SQL Server 2000 you can run the wizard by clicking once on the registered SQL Server instance in Enterprise Manager, then click on the “Tools” menu, select “Wizards”, and select “Copy Database Wizard” in the category of “Management” wizards). Then you will be presented with the welcome screen of the Copy Database Wizard:

The SQL Server Copy Database Wizard - Article on SQLNetHub

Click on Next.

Then you have to specify the Source Server and the Authentication Method for accessing it:

The SQL Server Copy Database Wizard - Article on SQLNetHub
Click on Next.

With the following screen, you specify the Destination Server and the Authentication Method:

The SQL Server Copy Database Wizard - Article on SQLNetHub
Click on Next.

This screen allows you to choose the Transfer Method based on which the wizard will perform the copy/move database task:

The SQL Server Copy Database Wizard - Article on SQLNetHub

So here you are presented with two Transfer Methods: (i) Use the detach and attach method, and (ii) Use the SQL Management Object Method (SMO). The first method requires the database to go offline (that is, downtime) while the second requires no database downtime but it is slower than the first one. The choice is yours! After you select a Transfer Method click on Next.

You are then presented with a screen containing all the databases on the Source Server you specified earlier, where you can select which database(s) you want to Move or Copy. Note that even though you are presented with the SQL Server system databases on this screen as well, you cannot copy or move them (that wouldn’t make any sense right? :)). For this example I used the TestDB database and I want to copy it locally on the same SQL Server instance, so I just selected the Copy checkbox for this database (the procedure is the same for the Move option with the difference that the Wizard will delete the database from the Source Server after moving it to the Destination):

The SQL Server Copy Database Wizard - Article on SQLNetHub
Click on Next.

The next screen allows you to configure the destination database. To this end, you can specify the new database’s name, the database files paths and names, and also instruct the wizard what to do in the case the destination database already exists:

The SQL Server Copy Database Wizard - Article on SQLNetHub
Click on Next.

So, what does actually the Copy Database Wizard do? It receives all the necessary parameters by the user, and it builds a SSIS Package! On the following screen you can configure the package:


Click on Next.

Last but not least, with another screen, you are presented with the option of when to run the SSIS Package. You can either run it immediately or schedule it to run later. Also on this screen, you must select an Integration Services Proxy account which will allow SQL Server Agent to access and run the SSIS Package:

The SQL Server Copy Database Wizard - Article on SQLNetHub
Note: The SQL Server Agent service must be running for being able to execute the SSIS Package created by the Copy Database Wizard.

Then by clicking on Next you are presented with one last screen which contains a summary of the tasks the Copy Database Wizard has to do. You click on Finish and that’s it!

 

Considerations

While using the Copy Database Wizard in SQL Server 2005 or later, you must take into account some considerations. Some of them are:

  • You must be a member of the sysadmin fixed server role on both the Source and Destination Database Servers.
  • Your SQL Server installation should include SQL Server 2005 Integration Services (SSIS) or later.
  • When you choose to Move a database, the Wizard deletes it from the Source Server. When you choose to Copy a database, the wizard does not delete it from the Source Server.

For comprehensive documentation regarding the Copy Database Wizard you can visit this link.

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Featured Online Courses:

 

Read Also:

 

Check our other related SQL Server Administration articles.

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub