Step-by-Step Guide

Step 5: Migrate using the Copy Database Wizard

Follow the steps below for the Copy Database Wizard to successfully migrate the needed databases and dependent objects in unison.

How to upgrade with the Copy Database Wizard
ID Directions Screenshot
1 Load the wizard: Start SQL Server 2005 Management Studio. Then right click on the database. On the pop-up screen, select the "Tasks" and "Copy Database" options. This will load the Copy Database Wizard.
2 Select the source server: Select the SQL Server 2000 server and authentication mode. Press the Next button to continue the wizard.
3 Select the destination server: Select the SQL Server 2005 server and authentication mode. Press the Next button to continue the wizard.
4 Choose your transfer method: The two options are to move by detaching and attaching the database; or to use SMO to migrate the data.

For this example, we will use the detach and attach option because we have a defined window to migrate

    Requires Free Membership to View

the entire database without any users in the system.

Press the Next button to continue the wizard.

5 Select the databases: Select the databases to migrate from SQL Server 2000 to 2005. Keep in mind an option is available to Move as well as Copy the databases. With the move option, the database will only reside on the SQL Server 2005 server, but the copy option will keep a version on the SQL Server 2000 server after the migration to 2005. Select the check boxes for the applicable databases. In this example, we will move the mt2000 database and copy the Surveys database.

Press the Next button to continue the wizard.

6 Configure the destination database: Review the configurations and ensure the status is OK for the files.

Press the Next button to continue the wizard.



7 Move database objects: Use the arrow button (>>) to move the available objects to the selected objects.

Press the Next button to continue the wizard.

8 Name the package: Name the SSIS package and determine if the logging is to the Windows Event Log or to a text file.

Press the Next button to continue the wizard.

9 Schedule the package: Select the radio button to execute immediately or schedule it for a later datetime. Choose the proxy account.

Press the Next button to continue the wizard.

10 Complete the wizard: Review the summary output. If you need to make any changes, select the Back button to change the configuration. If the output is accurate, select the Finish button to begin the migration.
11 Review: Examine the migration process and validate that it completes properly. Then review the output file from the process to validate that it has no errors.


Upgrading to SQL Server 2005 using Copy Database Wizard
 Home: Introduction
 Step 1: Plan your migration
 Step 2: Choose your hardware and software
 Step 3: Test your migration process
 Step 4: Be sure you have a corruption-free environment
 Step 5: Migrate using the Copy Database Wizard
 Step 6: Perform post-upgrade tasks
 Step 7: Conclusion


ABOUT THE AUTHOR:   
Jeremy Kadlec
Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
Copyright 2006 TechTarget

This was first published in June 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: