Saturday, April 11, 2009

The SQL Server Copy Database Wizard

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 and 2008, 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.

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:


Click on Next.

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


Click on Next.


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


Click on Next.


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


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


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:


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:


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.

[Ads]
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: