Copy Database Wizard (MS SQL Server 2005 and later)

Introduction

Once at work, I came across a requirement where the development team needed the production database in the development environment as soon as possible. There were critical code changes to be tested without making any fuss about it. That is when Copy Database Wizard really came to my rescue. It’s a handy tool in such situations or when you need an upgrade of your database.

I was able to get the database closest to the developers, allowing them complete flexibility to play around with it and test their changes rigorously. The other advantage is that we can transfer all the database objects like Tables, Procedures and Views, Logins, related SSIS packages, SQL Agent Jobs etc., unlike import/export where you can only get the necessary objects (tables) to the destination environment. Contrary to customary upgrades for SQL Server 2000 or earlier versions, where manual detaching (from source server) and re-attaching (to the destination server) of the database files was involved, this option takes care of them at the product level.

The Copy Database Wizard can be used to copy or move a database and all its associated objects to any server.

• While upgrading server, the Copy Database Wizard is a quick way to move data to the new system.
• Use the wizard to create a backup copy of the database on another server, ready to be used in case of emergency.
• Developers can copy an existing database and use the copy to make changes without endangering the live database.

The Copy Database Wizard proves to be a valuable tool for administrative functions.

Note: The Copy Database Wizard is available for SQL Server 2005 or later versions.

Now that we know how useful this tool is, let’s get started and learn it.

Create a sample database (SampleDB) and add some tables (dbo.Students, dbo.teachers) and create a procedure GetTeacher.

We will transfer this complete database to a Second Instance of SQL Server.

Right click on the database >Tasks>Copy Database. Select a Source Server.

Next, select the destination server where you intend to Copy/Move the SampleDB database:

In our case, we are transferring it to the Second Instance.

Select OK and Click Next.

On the next screen, there are the following two options for copying the database:

1. Using detaching and attaching method
2. Using SMO (SQL Server Management Object) method.

Let’s get some insight into each of these options:

Option 1 gives the flexibility of very quick transfer to the destination on account of bringing the database offline until this transfer takes place. While this is a very good option when speed is the objective, it’s not advised when your database downtime is highly critical.

Option 2 uses the SMO option to transfer the database from the source to the destination. While this is a slower process and takes longer, it keeps the database online. This is a very good option for DBAs when the database cannot be brought down.

We’ll go with the SMO option for our demo.

The “Select Databases” screen lets you select all the databases that need to be moved/copied. Also the move (don’t keep the database at source) and copy (keep a copy of the database at source) options corresponding to each of the selected database gives the option to keep or do away with the source copy of the selected database.

Let’s select Copy SampleDB and click Next.

On the “Configure Destination Database” screen, provide the name of the destination database.

If the destination server already has a database with the same name, we have two options choose from:

1. Cancel the transfer and abort the job.
2. Drop the database on the destination, then continue the transfer overwriting the existing database file.

Let’s choose the first one and click Next.

On the “Select Server Objects” screen, we can choose to transfer the Stored Procedure on Master, SSIS packages, Logins, SQL Agent Jobs, or End Points configured on the source database.

Select what needs to be transferred and click Next.

The “Configure this package” screen allows us to save this as a package at a customized path (Package Location) for reusability. The job logging on the Windows event log is really beneficial in case of failures.

The “Schedule the package” option helps us to schedule this job (Package) at any particular time. In the case of scheduling, it’s important to keep the SQL Agent up and running. It will run the same at the scheduled time.

For demonstration purposes, let’s choose to run it immediately.

Click on Finish and the job runs.

This completes our job successfully.

For verifying if SampleDB has been transferred to the Second Instance server, check SSMS and Object Explorer. See if the objects, procedures, and logins have been transferred to the destination server successfully.

This completes the journey of Copy Database Wizard utility.