Preparing to Upgrade from SQL Server 6.5

Installing SQL Server

Installing SQL Server

Preparing to Upgrade from SQL Server 6.5

Follow this checklist before using the SQL Server Upgrade Wizard to move from Microsoft® SQL Server™ version 6.5 to Microsoft SQL Server 2000:

  • Back up the SQL Server 6.5 database files (all .dat files, including master) so you can completely restore them if necessary.

  • Run the appropriate Database Console Commands (DBCC) on the SQL Server 6.5 databases to ensure they are in a consistent state.

  • Estimate the disk space required. In addition to the hard disk space used by Microsoft SQL Server 2000, you need approximately 1.5 times the size of the SQL Server 6.5 databases.

  • Set tempdb to at least 10 MB in the SQL Server 6.5 installation, 25 MB is recommended.

  • Ensure the master database has at least 3 MB of free space.

  • Ensure that all database users have logon information in the master database.

    This is important for restoring a database because system logon information resides in the master database.

  • Ensure the @@SERVERNAME is defined on SQL Server 2000. If @@SERVERNAME is NULL, you can use the sp_addserver system stored procedure. For example, if your computer is named production, the command would be sp_addserver 'production1',local. Changes do not take affect until the MSSQLServer service is restarted.

    Note  Because SQL Server 6.5 does not recognize the hyphen (-) in a computer name, replace a hyphen with an underscore (_).

  • Disable any startup stored procedures.

    The SQL Server Upgrade Wizard starts and stops the SQL Server 6.5 server during the upgrade process. Stored procedures processed at startup may cause the upgrade process to stop responding.

  • Ensure that you upgrade all databases with cross-database dependencies at the same time.

    For example, you want to upgrade three databases, database1, database2, and database4, and there is logon information in SQL Server 6.5 master..sysdatabases for USER1 that defaults to database3 (not one of the databases you are upgrading). The SQL Server Upgrade Wizard does not create the logon information because the database is not upgraded, and therefore does not exist in SQL Server 2000. If USER1 is listed as the owner for objects in any of the databases upgraded, those objects cannot be created because the logon information for USER1 does not exist.

  • If performing a two-computer upgrade, assign a domain user name and password to the MSSQLServer service for SQL Server 6.5 and SQL Server 2000 instead of using the local system account or a local user account. The domain user account should belong to the Administrators group of both the computers involved in the upgrade. (The local system account is sufficient for a one-computer upgrade.)

  • Stop replication and ensure that the log is empty.

  • Quit all applications, including all services dependent on SQL Server.

If you copied the SQL Server 6.5 databases to a new computer to perform the upgrade, you may need to update the new SQL Server 6.5 master database as follows:

  • Change references from the earlier server name to the current server name in the SQL Server 6.5 master database.

  • Update the device file locations in the SQL Server 6.5 master database.

  • Ensure all users have corresponding logon information.

To change the size of tempdb in SQL Server 6.5