Click here to Skip to main content
Click here to Skip to main content

Copy Database Wizard (MS SQL Server 2005 and later)

, 11 Mar 2011
Rate this:
Please Sign up or sign in to vote.
How the Copy Database Wizard can be helpful to users working on upgrades and migration projects.

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.

Advantages Disadvantages
  • Very quick transfer.
  • Advised when the destination database’s immediate availability is critical compared to the source database going offline.
  • This option takes your database offline for the time the transfer is in progress. Users/Application will not be able to connect to the database.
  • Not a good option when the source database’s 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.

Advantages Disadvantages
  • Slow data transfer.
  • Advised when the source database availability is highly critical.
  • This option keeps your database online for the time the transfer is in progress. Users/Application will be able to connect to the database invariably.
  • Not a good option when the destination needs database as soon as possible and critically.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Keshav Singh
Database Developer
India India
I am a Microsoft certified Technology Specialist in MS SQL Server 2008 and 2005.I have fair amount of experience and expertise in MS database development, administration and modeling and MS BI. Started my career in 2007 and primarily into MS databases, and later diversified into MS BI especially SSIS. I also have a little exposure in Oracle 10g while working on one of the migration projects. But MS SQL Server is my passion!
 
Firm believer in knowledge grows from sharing, I really like reading books, trying new features and sharing the little that I know. Unless we're willing to have a go, fail miserably, have another go, success won't happen. The best thing I have discovered about myself is the "never say die" attitude. The best comment I have ever received is when my manager at my first job said "when this guy works he throws away his watch".

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberrjwh14-Mar-13 4:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 11 Mar 2011
Article Copyright 2011 by Keshav Singh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid