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:
With the following screen, you specify the Destination Server and the Authentication Method:
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):
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:
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:
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:
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!
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.
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).