How to perform a version upgrade using a tape drive (SQL Server Upgrade Wizard)

To perform a version upgrade using a tape drive

1. On the Start menu, point to Programs/Microsoft SQL Server-Switch, click SQL Server Upgrade Wizard, and then click Next.
2. Click Tape; then click Next.
3. In Export server (6.x), in the Server name box, enter the name of the computer on which Microsoft® SQL Server™ version 6.x resides.
4. In the Administrator password (‘sa’) box, enter the sa password for SQL Server 6.x, and then click Next.

   Unless you have changed it, the sa password for SQL Server 7.0 is blank.

5. Include the databases to upgrade. Move any database not to be upgraded at this time to the Exclude list; then click Next.
6. In Device for data transfer, specify the location of the tape drive.
7. In 6.x device backup options, select Backup 6.x devices before exporting data if you have not backed up the databases already.

   Prior to creating the SQL Server 7.0 databases, the SQL Server Upgrade Wizard either prompts you to back up the SQL Server 6.x devices or copies the devices for you automatically.

8. Select Delete 6.x devices before importing data if necessary due to lack of disk space; then click Next.

   After objects and data are exported, and before creating databases in SQL Server 7.0, the SQL Server Upgrade Wizard deletes the SQL Server 6.x devices to reclaim disk space.

9. Select Use the default configuration or edit the default; then click Next.

   Click Edit to examine and make changes to the proposed disk configuration within the layout utility. When you have finished the layout utility, close it to return to the SQL Server Upgrade Wizard.

10. In System objects to transfer, select the object types to transfer from SQL Server 6.x to SQL Server 7.0:
    • Server configuration

      Login and remote login registrations and server configuration options relevant to SQL Server 7.0 are transferred as part of the version upgrade.

    • Replication settings

      All articles, subscriptions, and publications of each selected database, plus the distribution database, if any, are transferred and upgraded.

    • SQL Executive settings

      All tasks scheduled by SQL Executive are transferred and upgraded so that SQL Server 7.0 can schedule and run those tasks in SQL Server Agent.

11. In ANSI Nulls, select:
    • Off, if ANSI nulls should not be used when stored procedures are created. This is the default.
    • On, if ANSI nulls should be used when stored procedures are created.
12. In Quoted Identifiers, select one of these options, and then click Next:
    • Mixed (or don’t know), if some of your objects were created with QUOTED_IDENTIFIER set to ON and others with it set to OFF, or if you are not sure how they were created.
    • Off, if all objects should be compiled with QUOTED_IDENTIFIER set to OFF.
    • On, if all objects should be compiled with QUOTED_IDENTIFIER set to ON.
13. If all options are correct, click Finish.

    The SQL Server Upgrade Wizard performs a version upgrade using the options you specified. The wizard upgrades the server in the following order:

    • Starts SQL Server 6.x.
    • Updates ODBC and SQL-DMO components on SQL Server 6.x.
    • Examines SQL Server 6.x databases.
    • Exports replication settings.
    • Exports server configuration settings from the master database.
    • Exports logins.
    • Exports database owners.
    • Exports SQL Executive objects and settings from the msdb database.
    • Exports database objects for all databases chosen.
    • Shuts down SQL Server 6.x.
    • Exports data to tape.
    • Backs up and then deletes SQL Server 6.x devices.
    • Starts SQL Server 7.0.
    • Creates databases.
    • Modifies SQL Executive objects and settings to SQL Server 7.0 formats.
    • Imports logins.
    • Imports database objects.
    • Imports data from tape into SQL Server 7.0.
    • Imports modified SQL Executive objects and settings into SQL Server 7.0.
    • Imports replication settings.
    • Examines SQL Server 7.0 databases.
    • Verifies that the upgrade is successful.
    • Sets database options in SQL Server 7.0.
    • Marks server and databases as moved (for future runs of the wizard.)
    • Drops temporary tempdb files.

The SQL Server 6.x catalog data, objects, and databases are upgraded and converted so that they are compatible with SQL Server 7.0. After the version upgrade is complete, SQL Server 7.0 becomes your production system.