If you have SQL Server 6 or 6.5, you can upgrade your databases using the SQL Server Upgrade Wizard.

Note that the Upgrade Wizard may remove the old databases, but it does not remove the SQL Server software itself - this will remain on your machine.

You have some requirements to meet before you perform an upgrade:

• 1.5 times the disk space currently used by the database to be upgraded.
• NT Server operating system with Service Pack 4.
• Service Pack 3 for SQL Server 6 or 6.5.
• The Network Protocol must be "Named Pipes".

Before performing an upgrade, you should:

• Back up all your old databases files
• Install SQL Server 7.
• Run consistency checks on your databases (DBCC CHECKDB ('database name').
• Make sure your 6.x tempdb has 25 MB.
• Ensure that all your users have logins in the master database, and that their default database will exist in SQL Server 7.0 after the upgrade.
• Disable any stored procedures in your database that run on startup, because during the upgrade, the SQL Server Service will be stopped and started. If the stored procedures are enabled, they would start running and could make the upgrade process hang.
• Stop replication, if running.
• Shut down all of your applications and disconnect all of your users.

Some terminology you need to know:

"Side-by-side" upgrade - this is where only one computer is used for the upgrade.

"Computer-to-computer" upgrade - this is where you use two computers for the upgrade.

"Named Pipes" upgrade - Data is moved from the old to the new database via a direct connection.

"Tape Backup" upgrade - the databases you want to migrate are backed up, the originals deleted, and then the data loaded into SQL Server 7.

Steps for a Named Pipes Upgrade

• Go to Programs, Microsoft SQL Server-Switch, SQL Server Upgrade Wizard, and then click Next.
• Choose Named Pipe.
• Type the name of the computer with SQL 6.x (Note: You can't use Named Pipes upgrade with version 6).
• Enter the System Administrator passwords for both servers.
• Choose the database/s you would like to upgrade.
• Choose to use the default disk config, or else edit it using the layout utility.
• Select which object types should be upgraded.
• Choose whether or not you want to allow ANSI NULLs.
• Choose the Quoted Identifiers setting - this needs to be on if any of your tables etc use keywords in their names.
• Click Finish!

This is what happens next:

• The upgrade Wizard starts the SQL Server 6.x.
• It examines the database/s.
• It exports the system objects, user objects and logins.
• It stops SQL Server 6.x.
• It starts SQL Server 7.
• The new database/s are created
• SQL Executive Objects and settings are changed to SQL Server 7 format.
• Logins and objects are created.
• Data is transported from the old to the new database.
• Changed SQL Executive objects and settings are loaded into SQL Server 7.
• The upgrade Wizard now verifies the new databases and checks that the upgrade was successful.

NOTE: The above method is the faster way to do an upgrade. After you finish, both 6.x and 7.0 databases will exist.

Steps for a Tape Backup Upgrade

The process you go through to do a Tape Backup upgrade is very similar to that described for a Named Pipes upgrade, but:

• You must choose the Tape option rather than the Named Pipes option.
• You'll need to put in the location of the tape drive.
• You have the option of doing a normal full backup during the process.

The process that the upgrade Wizard goes through when you click on Finish, again, is similar, with the following differences:

• After shutting down SQL Server 6.x, the Wizard moves (not copies) the data to tape.
• If selected, it backs up the database/s.
• ALL 6.x databases are now deleted (whether you selected them for upgrade or not).
• Once SQL Server 7 has been started and the objects and settings created and configured, then the data is imported from tape.

NOTE: The above method is slower. Once you've finished, ONLY SQL Server 7 databases will exist!