Upgrade SQL Server with the Copy Database Wizard
This topic describes how to use the Copy Database Wizard to upgrade a SQL Server database to a later version.
When using the Copy Database Wizard to upgrade a database, adhere to the following requirements:
* Before upgrading, make sure that no applications or services are trying to access the database. Do not use read-only mode, as this will result in an error.
* You cannot rename the database during this operation.
To upgrade a SQL Server 2000 database to a later version
1. Connect to any instance of SQL Server Database Engine using Object Explorer in SQL Server Management Studio.
2. Expand Databases, right-click a database, point to Tasks, and then click Copy Database.
3. Complete the steps in the wizard.
To ensure optimal performance of an upgraded database, run sp_updatestats (update statistics) against the upgraded database.
After you use the Copy Database Wizard to upgrade a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008, the database becomes available immediately and is then automatically upgraded. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the Full-Text Upgrade Option server property. If the upgrade option is set to Import or Rebuild, the full-text indexes will be unavailable during the upgrade. Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to Import, if a full-text catalog is not available, the associated full-text indexes are rebuilt.
You can detach a SQL Server 2000 database from the SQL Server 2000 instance and attach it to the SQL Server 2005 instance. Then simply point the clients to the new server. Consolidating from SQL Server 2000 to SQL Server 2005 is basically the same as consolidation from SQL Server 2005 to SQL Server 2005.
Look at the last section of this article.