INF: Backup Strategies and Tips Before Upgrading SQL Server

• Microsoft SQL Server, versions 4.21a and 6.0

SUMMARY

This article describes some backup and upgrade strategies that you can perform before upgrading SQL Server. In addition, there are helpful tips included for performing a successful SQL Server upgrade operation. You should review the Microsoft SQL Server documentation on upgrading SQL Server before performing any SQL Server upgrade.

MORE INFORMATION

BACKUP

During any upgrade application process, it is recommended that you have a valid backup. Because Windows NT Server provides a file backup facility and SQL Server provides dump and load capabilities, it is best to perform two types of backup.

WINDOWS NT SERVER BACKUP

When using the Windows NT Server backup utility, stop SQL Server and back up all the data and log device files, including Master.dat (which, by default, are those files that end with a .dat extension). If you are unsure about the file name, go to a query window, set your database to master, and run the following command:

   select * from sysdevices where cntrltype = 0 or cntrltype = 2

Copy all the data and log device files to tape or to a temporary directory other than SQL, SQL60, or any of their subdirectories; this prevents accidental deletion of the SQL directory. It is a good idea to have a recent backup of the registry. To learn how to back up the local registry to tape, see the "Backing Up Disk Files to Tape" section of the Windows NT Server documentation. After the upgrade successfully completes, you can delete the temporary directory.

If time or space is limited, you can make a copy of the Master.dat file and do a full dump all of the databases. See the next section of this article, SQL SERVER DUMP, for details on how to dump all of the databases.

SQL SERVER DUMP

It is very useful to have the results of SELECT statements against the sysdatabases, sysdevices, and sysusages table in the master database. To do this, perform the following steps:

1. Go to a query window and run the following:

         /********************************/
         use master
         go
         print 'select * from sysdatabases'
         select * from sysdatabases
         print 'select * from sysdevices'
         select * from sysdevices
         print 'select * from sysusages'
         select * from sysusages
         /*********************************/
   

2. Save the results to a directory other than SQL, SQL60, or any of their subdirectories.

3. Start SQL Server in single-user mode by going to a Windows NT Server command prompt, changing to the Sql\Binn or Sql60\Binn directory, and running the following command:

         sqlservr -c -m
   

4. Minimize the command prompt window.

5. Run sp_helpdb, DBCC CHECKDB, and DBCC NEWALLOC on each database. The script below will facilitate this for you. Save this script in a text file.

         /*******************************************************/
   

         set nocount on
         go
   

         create table #tblDatabases
         (
         strName varchar(30) not null
         )
         go
   

         insert into #tblDatabases
            select name from sysdatabases where name <> 'tempdb'
         go
   
         print ''
         print 'select * from master..sysdatabases'
         print 'go'
         print ''
   
         declare @strDatabase varchar(30)
   
         set rowcount 1
         select @strDatabase = strName from #tblDatabases
         set rowcount 0
   
         while((select count(*) from #tblDatabases) > 0)
         begin
            print 'exec sp_helpdb '
            print @strDatabase
            print 'go'
            print 'print ""'
            print 'go'
            print ''
   
            print 'dbcc checkdb('
            print @strDatabase
            print ')'
            print 'go'
            print 'print ""'
            print 'go'
            print ''
   
            print 'dbcc newalloc('
            print @strDatabase
            print ')'
            print 'go'
            print 'print ""'
            print 'go'
            print ''
   
            if(@strDatabase <> 'master')
            begin
               print 'exec sp_dboption '
               print @strDatabase
               print ',"read only", FALSE'
               print 'go'
               print 'print ""'
               print 'go'
               print ''
            end
            else
            begin
               print 'exec sp_defaultdb sa, "master"'
               print 'go'
               print 'print ""'
               print 'go'
               print ''
            end
   
            delete from #tblDatabases where strName = @strDatabase
   
            set rowcount 1
            select @strDatabase = strName from #tblDatabases
            set rowcount 0
         end
   
         go
   
         /*******************************************************/
   
   

6. Go to another Windows NT Server command prompt window, change to the Sql\Binn or Sql60\Binn directory and run the following command:

         isql -U sa -P <password> -S <server_name> -n -i <input_file> -o
         <output_file>
   

7. After this operation completes, use the output file that you created as the input file and use the following command to create a new output file for the results of the next query:

         isql -U sa -P <password> -S <server_name> -n -i <output_file> -o
         <new_output_file>
   

8. Use the following article in the Microsoft Knowledge Base to assist you in scanning the new output file for errors:

         ARTICLE-ID: Q115519
         TITLE     : INF: How to Scan SQL Errorlog or DBCC Output for Errors
   
   

9. If the databases are clean, proceed to dumping each database by running the following command:

         isql -U sa -P <password> -S <server_name> -Q "dump database
         <database_name> to <dump device name>"
   

10. After that operation completes, take SQL Server out of single-user mode by closing both command prompt windows and starting up SQL Server as a service, using either the SQL Service Manager, SQL Enterprise Manager, or MSSQLSERVER in the Services Control Panel.

CONFIGURATION

Set the default database of the system administrator (SA) to master by typing the following command at a query window:

   sp_defaultdb sa, master

   ARTICLE-ID: Q135349
   TITLE     : FIX: SQL 6.0 Upgrade Fails if SA Default DB is Not Master

Check the SQL Server documentation to determine how much physical disk space is required for the upgrade. The following are some other relevant articles that concern disk space that you should read before upgrading SQL Server:

   ARTICLE-ID: Q149650
   TITLE     : BUG: Upgrade Fails if Not Enough Space on Master for Tempdb

   ARTICLE-ID: Q149566
   TITLE     : BUG: Upgrade/Install Fails if Model DB is Larger Than Msdb

Upgrading a database requires all objects to be scanned. In doing so, the upgrade may exceed some of your current runtime configuration values. To avoid this, you may want to temporarily double the size of the 'open objects' and 'locks' options before the upgrade. If you chose to do this, make sure you have enough memory dedicated to SQL Server to accommodate the changed values.

Also, check how much memory is given to SQL Server. In SQL Server version 4.21a, the minimum value is 3,072 bytes (6 megabytes), while in SQL Server versions 6.0 and 6.5, the minimum value is 4,096 (8 megabytes). If you have additional physical RAM, increase the memory given to SQL Server to the level recommended in the SQL Server documentation. For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q110983
   TITLE     : INF: Recommended SQL Server for NT Memory Configurations

Next, execute sp_helpsort to determine what sort order and character sets you have installed. For SQL Server 6.0 and SQL Server 6.5, these values can be found in appendix A of the setup book under "About Character Sets" and "Sort Order Ids." For SQL Server 4.21a, these values can be found in the configuration guide.

Shut down SQL Server and close any open SQL Server windows before upgrading. In addition, make sure you are logged on to Windows NT Server as an administrator or someone with administrative privileges. Logging on with administrative privileges may still result in upgrade problems if the 'Full Control' permissions have been revoked for administrator at either the file level for NTFS partitions or at the registry level. Check that administrator and system accounts have full control in the registry hives listed in the TROUBLESHOOTING section of this article.

Finally, check the network capabilities. If the server that is being upgraded does not have network capabilities, stop the Workstation service to force the upgrade to use the local pipes. To do this, perform the following steps:

1. Open the Services Control Panel.

2. Select the Workstation service.

3. Click Close.

1. Open the Network Control Panel.

2. Click Add Adapter.

3. Select MS Loopback Adapter and click Continue.

Go to the SQL Server Setup.exe that you want to upgrade to and upgrade SQL Server. During the upgrade, it is best to specify the same drive and directory to copy the SQL Server files to. Also, make sure you know the correct password for the SA before upgrading.

NOTE: Upgrades from beta or evaluation versions of SQL Server are not supported.

WINDOWS NT SERVER VERSIONS FOR SQL SERVER

If you are planning on upgrading the computer to Windows NT Server 4.0, the sequence of upgrading SQL Server and Windows NT Server matters. For additional information on the Windows NT operating system versions that support Microsoft SQL Server versions 4.2x, 6.0, and 6.5, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q122352
   TITLE     : INF: Supported Windows NT Versions for SQL Server

TROUBLESHOOTING

If the upgrade encounters a problem while copying files, correct whatever error you received and try the upgrade again. If you encounter a problem after copying all the files, remove all SQL directories and files in Windows NT Explorer or File Manager and remove any entries in the registry for SQL Server. Be very careful when editing anything in the registry. If you delete a key that does not belong to SQL Server, you can restore the registry backup that you made earlier, in the WINDOWS NT BACKUP section of this article.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall SQL Server. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For SQL Server 4.21a, select and delete SQLServer and SQLMonitor found in the following registry keys:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServer

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServer

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLMonitor

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLExecutive

• Reinstall the original SQL Server version. After it is installed, stop the SQL Server services and replace all of the data and log device files, including Master.dat, into their respective directories. You can find these directories from the phyname column in the output from the sysdevices table. Fix whatever caused the upgrade to fail and try the upgrade again. If the upgrade still does not successfully complete, contact your primary support provider.
• Reinstall the SQL Server version that you are trying to upgrade to, then load the earlier version of the SQL Server databases. Note the user database dump (the master database is not allowed to be loaded or upgraded from earlier versions of SQL Server dumps) must be a full database dump, not a transaction log dump. Also, the SQL Server version that you are trying to upgrade to must be using the same sort order as the earlier version.

SUMMARY

The following list summarizes the steps recommended for a SQL Server upgrade:

1. Select * from sysdatabases, sysdevices, and sysusages, and save the results.

2. Perform file backups of all the data and log device files.

3. Run DBCC CHECKDB on each database.

4. Put the server in single-user mode and run DBCC NEWALLOC on each database.

5. If DBCC CHECKDB and DBCC NEWALLOC show no errors, dump databases while the server is in single-user mode.

6. Take SQL Server out of single-user mode.

7. Set the default database of the SA to master.

8. Make sure no databases are set to read-only.

9. Make sure you have enough disk space to perform the upgrade.

10. Run Chkupg.exe.

11. Resolve any keyword conflicts before upgrading.

12. Increase the size of the 'open objects' and 'locks' options.

13. Increase the 'memory' option to recommended levels, especially if the current value is 3072.

14. Make sure the value of the 'open databases' option is greater than the number of databases on your system.

15. Run sp_helpsort and save the results.

16. Shut down SQL Server before upgrading.

17. Make sure you either are logged on to Windows NT Server as an administrator or have administrator privileges and the administrator has full control permissions at the file and registry level. Also, make sure you know the SA password.

18. Check the network capabilities of the server you are upgrading. Test whether the server is on the network by typing "net view" at a command prompt; see whether you can view other computers on your network.

19. Upgrade SQL Server.

20. Specify the same drive and directory to copy the new files to.