Lesson 4: Restoring and Rebuilding System Databases

3 4

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.


After this lesson, you will be able to

  • Restore the master database from backup
  • Rebuild the system databases

Estimated lesson time: 15 minutes


Restoring the Master Database

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.

 Sqlservr -m 

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.

 figure 9.27 - starting sql server 2000 as an application in a command-prompt window.

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.

figure 9.28 - restoring the master database.

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.

Rebuilding the System Databases

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.

Note


Remove the Read-only attribute from the original installation files, orthe Rebuildm utility will fail.

 figure 9.29 - rebuilding the master database.

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.

 figure 9.30 - the rebuild master warning dialog box.

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.

Lesson Summary

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.



MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
ISBN: N/A
EAN: N/A
Year: 2001
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net