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:

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.

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 Star 2 Stars 3 Stars 4 Stars 5 Stars (1 votes, average: 5.00 out of 5)

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

Artemakis Artemiou

Artemakis Artemiou, a distinguished Senior Database and Software Architect, brings over 20 years of expertise to the IT industry. A Certified Database, Cloud, and AI professional, he earned the Microsoft Data Platform MVP title for nine consecutive years (2009-2018). As the founder of SQLNetHub and GnoelixiAI Hub, Artemakis is dedicated to sharing his knowledge and democratizing education on various fields such as: Databases, Cloud, AI, and Software Development. His commitment to simplicity and knowledge sharing defines his impactful presence in the tech community.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.