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.
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 2006TechTarget
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.
Home: 
Jeremy Kadlec
