If your master database becomes corrupt or if you lose your entire SQL Server 2000 installation, you will need to either restore your master database or rebuild your system databases. In this lesson you will learn how to restore the backup of your master database. You will also learn to rebuild your system databases if they are no longer functioning.
If your master database is functioning but damaged in some fashion (such as the deletion of all logins), you can restore the master database using the most recent full database backup of the master database. Any changes to the master database since the most recent database backup will be lost. You should script database objects when you create them and save the scripts. You should also mirror the system databases using RAID 1 where possible.
To restore the master database, start SQL Server 2000 in single-user mode with the -m option in the Command Prompt window or from the Run dialog box.
The preceding command starts SQL Server 2000 as an application in a command-prompt window. The text you see when you start SQL Server 2000 as an application is the same text you see in the SQL Server error log. See Figure 9.27.
Starting SQL Server 2000 as an application in a command-prompt window.
Next, start SQL Query Analyzer and restore your most recent backup of the master database using the same commands you use to restore any user database.
RESTORE DATABASE master FROM MasterFullBackup
The preceding example restores the master database from the MasterFullBackup backup device. See Figure 9.28.
Restoring the master database.
After the restoration of the master database is complete, the SQL Server 2000 application running in single-user mode will stop. You can then restart SQL Server 2000 normally. Assuming that your backup of the master database was current, you are back in business. If not, you will need to re-create database objects and perhaps reattach user databases. You may also need to restore other system databases, depending upon the reason for the restoration of the master database.
If your master database has ceased functioning, you cannot recover quite this easily. In this case, you must rebuild the system databases from scratch (or possibly reinstall SQL Server 2000). To rebuild the system databases, use the Rebuildm.exe utility located in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn folder. When you use the Rebuildm utility, you use the original data files for each of the system databases to rebuild each system database to their original condition in the Rebuild Master dialog box. You must have the original installation files available, either on a local drive or on a network drive. See Figure 9.29.
Rebuilding the master database.
Click the Rebuild button to begin the process. You receive a warning in a Rebuild Master dialog box that you are about to rebuild and overwrite all of your system databases. See Figure 9.30.
The Rebuild Master warning dialog box.
After the rebuild is complete, you will need to restore your master database in the manner described earlier. Next, restore each system database, particularly the msdb database. If you have customized the model database, restore it. If you are using replication, you will need to restore the distribution database. Replication is covered in Chapter 15. Finally, you may need to restore or reattach any system databases that were affected by the failure of the system databases.
If your master database becomes corrupt, you may need to restore or rebuild it. Restoring the master database requires starting SQL Server 2000 in single-user mode. Rebuilding the master database requires rebuilding all system databases using the original installation data files for the system databases. If you must rebuild the system databases, restore these databases from backup to recover to the point of your most recent backups. Any system database activity since your most recent backups will have to be manually regenerated. Finally, reattaching user databases may be required. This is faster than performing a full restore.