Step 5: Migrate using the Copy Database Wizard

When upgrading to SQL Server 2005, refer to this Step-by-Step Guide for the Copy Database Wizard to successfully migrate the needed databases and dependent objects in unison.

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 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


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
 

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

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close