Step 5: Migrate using the Copy Database Wizard

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Strategy and planning SQL Server database design disasters: How it all starts Can you shrink your SQL Server database to death? Tuning SQL Server performance via memory and CPU processing Tuning SQL Server performance via disk arrays and disk partitioning Virtual database storage for SQL Server: Friend or foe? SQL Server high availability when upgrading to SQL Server 2005 Secure SQL Server from SQL injection attacks How insiders hack SQL databases with free tools and a little luck Storage area network (SAN) basics every SQL Server DBA must know Tips for moving from SQL Server local disk storage to SANs SQL Server installation SQL Server consolidation: Why it's an optimization technique SSIS error message due to installation problem on SQL Server 2005 Get SQL Server log shipping functionality without Enterprise Edition How to create a SQL Server linked server to DB2 Tuning SQL Server performance via disk arrays and disk partitioning Virtual database storage for SQL Server: Friend or foe? Tutorial: Migrating to SANs from local SQL Server disk storage How to restore SQL Server database to transition server during upgrade Storage area network (SAN) basics every SQL Server DBA must know Tips for moving from SQL Server local disk storage to SANs SQL Server installation Research SQL Server database design and modeling SQL Server database design disasters: How it all starts Physical data storage in SQL Server 2005 and 2008 SQL Server 2008 data types: Datetime, string, user-defined and more Enforcing data integrity in a SQL Server database SQL Server and data manipulation in T-SQL Supertype and subtype tables in SQL Server SQL Server database design disasters: What not to do Check SQL Server database and log file size with this stored procedure SQL Server tempdb best practices increase performance FAQ: SQL Server databases how-to

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary binary tree  (SearchSQLServer.com) block  (SearchSQLServer.com) data structure  (SearchSQLServer.com) DDBMS  (SearchSQLServer.com) entity-relationship model  (SearchSQLServer.com) initial extent  (SearchSQLServer.com) primary key  (SearchSQLServer.com) segment  (SearchSQLServer.com) tablespace  (SearchSQLServer.com) view  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary