Upgrading Databases from SQL Server 7.0 (Copy Database Wizard)

As an enhancement to the regular upgrade procedure, you can perform an online upgrade of databases and associated meta data. Using the Copy Database Wizard, you can move or copy a database from Microsoft® SQL Server™ 7.0 to an instance of Microsoft SQL Server 2000, without having to shut down any servers in the process.

Advantages of an online database upgrade include:

No downtime for servers during the upgrade.
Custom selection of databases to upgrade, leaving other databases still available to the original (SQL Server 7.0) server.
Inclusion of related meta data in the upgrade procedure. For example, logon information, jobs, and user-specific objects associated with user databases can be included.
The process can be run at a convenient time.

The Database Copy Wizard is based on detach and attach functionality that allows user databases to be moved or copied from a source to a destination server. A Data Transformation Services (DTS) package performs the actual move or copy operation You can schedule the package to run at a specified time or rerun the package if required.

Options for SQL Server 7.0 Database Upgrades

Database administrators can move or copy one or more databases from an instance of SQL Server 7.0 to the default instance of SQL Server 2000 on your local computer, or to either a default or a named instance on a remote computer. This upgrade feature does not support SQL Server 6.5 databases.

Local computer

SQL Server 7.0 databases can be upgraded to a named instance of SQL Server 2000 on the local computer.

Remote computer

SQL Server 7.0 databases can be upgraded to a default instance of SQL Server 2000 on a remote computer.
SQL Server 7.0 databases can be upgraded to a named instance of SQL Server 2000 on a remote computer.

Note You can have only one active default instance of SQL Server on a computer at one time; either a default instance of SQL Server 7.0 or a default instance of SQL Server 2000. SQL Server 6.5 can also be a default instance. For more information, see Working with Instances and Versions of SQL Server.

Exceptions

The Copy Database Wizard cannot be used in these situations:

A database with the identical name on both source and destination servers cannot be moved or copied. On the database selection screen, it will be noted as "Already exists."
For databases involved in replication, a regular server upgrade is required.

Copy Database Wizard Safeguards

At the start of a database move or copy operation, one administrator must have exclusive use of all files to prevent any changes to the file set during the process. Two connections are required to copy database files: sysadmin privileges on both installations of SQL Server and administrator privileges on the server/network.

To prevent any chance of data corruption, the SQL Server 7.0 databases must be in read-only condition and cannot be renamed during this operation. Any name conflicts between source and destination servers must be resolved manually prior to upgrading databases. Nothing on the destination server is overwritten.

If you move or copy multiple databases in one operation, each database is actually moved one at a time; that is, one database at a time is detached, files are copied and then reattached. To avoid any problems, the DTS package writes a message to the error log indicating that the database is about to be detached from its source server. At the same time, a script is prepared to attach the database to its destination. After the database is successfully attached to the destination, another entry is written to the log indicating successful completion.

When upgrading to a destination that is a clustered server, the Copy Database Wizard will ensure you select only shared drives on a clustered destination server. The source server may also be clustered.

Note Unrelated to this upgrade process, you can also use the Copy Database Wizard to move or copy user databases from one instance of SQL Server 2000 to another instance of SQL Server 2000. For more information, see Using the Copy Database Wizard.

To upgrade databases online using the Copy Database Wizard