Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


- A -

Restoring the master Database


The master database contains all of the settings for SQL Server. It contains all of the information that SQL Server uses to find and open the devices, and the databases within those devices. It also contains SQL Server settings, and all of the SQL login IDs. The master database can be thought of as the registry for SQL Server.

If the master database gets damaged or corrupted, you need to either restore it from a previous backup, or recreate it manually.

As you learned in Day 8, "Backing Up and Restoring," it is relatively easy to highlight a database. Choose Backup/Restore, pick the database's backup; SQL Server then restores your database. The master database is the one exception, as it can't be restored in this fashion. Restoring the master database is a more involved process, as SQL Server protects the master from casual overwrites this way.

There are two general ways to restore the master database.

It is always better to restore the master from a current backup, if a valid backup is available. Rebuilding the master database manually gets your databases back online, but all of your configurations and users will have been lost, and must be recreated.

The two scenarios are discussed in detail here.

Restoring with a Good Backup

Figure A.1 is a diagram of a typical SQL Server. There are four devices, two for each database. The master database contains the record of those databases, as well as the configurations for SQL Server and all of the user login IDs.

If (or when) the master database gets corrupted, the scenario looks like Figure A.2--that is, the databases, database devices, and backup devices are all still good, but because the master database is no longer valid, SQL Server has no idea about those items.

If the master database were any other database, it could be restored easily via Enterprise Manager, using the methods shown in Day 8. Because the master database is so critical to SQL Server, there are special steps that must be taken to restore it, so that inadvertent overwrites are virtually impossible.

Figure A.1. Diagram of a typical SQL Server.

Figure A.2. Diagram of a SQL Server with a damaged master database.

These are the steps for restoring the master database when a valid backup is present:

After these steps, your SQL Server should be back up and running like it was before the crash.


WARNING: Your success in using this method depends on how up-to-date your backup copy of the master database is. For best results, back up your master database after every major change to SQL Server, and on a regular basis (once a week is a good idea). Use the Database Maintenance Plan Wizard (Day 8), or a scheduled task (Day 18, "SQL Server Automation") to automate your backups of the master database.

Rebuilding the master Database

If your master database is damaged, corrupted, or just plain missing, SQL Server may not even start. In this case, you need to rebuild your master database.


WARNING: Rebuilding the master database does not fix your old one, rather, it resets it to the generic, clean master database identical to the one with which you started.

To rebuild your master database, run the SQL Setup program off either the CD-ROM or from the \MSSQL\BIN folder. Choose Rebuild Master Database from the menu (see Figure A.3) and answer the location and size prompts.


WARNING: You have to choose the same sort order and character set that you originally had in order to restore a backup of the master database.

Figure A.3. Choosing to rebuild the master database.

Rebuilding the master database also resets the model, tempdb, and msdb databases.

After the master database has been rebuilt, the server looks something like Figure A.4--a brand new SQL Server with no database devices or databases other than the defaults.

Starting SQL Server in Single-User Mode

The backup copy of the master database must be restored in order to reestablish the links to the user databases (as well as bring back your configurations and other master database settings). SQL Server only allows the master database to be restored when running in single-user mode, in order to prevent accidental overwrites of the master database.

SQL Server must be started at a COMMAND prompt with the /m switch to force it into single-user mode.

Figure A.4. Diagram of a typical SQL Server with a new master database.


TIP: Although the /m switch is technically called the single-user mode, you can remember the /m switch by thinking of it as the Maintenance mode, because you can perform high-level maintenance (like restoring the master database) by using the /m switch.

Another trick when starting SQL Server in single-user mode is to make sure the SQL Executive service is stopped, as it sometimes interferes with single-user mode operations.


In order to start SQL Sever in Maintenance mode, you must first stop it, and then open a COMMAND prompt. Issue the following command from a COMMAND prompt:

C:\> sqlservr.exe /dc:\mssql\data\master.dat /c /m

To explain this command, you should examine the sqlservr.exe program's syntax.

sqlservr [/c] [/dmaster_device_path] [/f] [/eerror_log_path] 
     [/m] [/n] [/pprecision_level] [/rmaster_mirror]
     [/sregistry_key] [/Ttrace#] [/x]

/c indicates that you want to run SQL Server independent of being a Windows NT service (hence, it is controlled entirely from within the Command Prompt window).

/d is the path and filename of your master database device.

/f specifies that SQL Server should start with the minimum configuration options.

/e is the path and filename of your error log.

/m indicates that SQL Server should start in single-user mode.

/n specifies that you don't want to use the Windows NT event log for SQL Server messages.

/p indicates the level of precision you want to use for decimal and numeric numbers.

/r is used to specify the location of the mirrored master database device when your original master is damaged and you are using SQL Server mirroring for your master device.

/s means that you'd like to start SQL Server with an alternate registry key. This is an advanced option and won't be examined further here.

/T specifies a trace flag with which to start SQL Server. You may have need to use trace flags during advanced recovery or SQL Server troubleshooting. However, they are advanced options that aren't examined here.

/x disables the maintenance of CPU statistics for SQL Server.


NOTE: With any of these switches for sqlservr.exe, the / and the - characters can be used interchangeably. Therefore, the following two lines of code are the same command:
	  Sqlservr -m
	  Sqlservr /m

The Command Prompt screen should look like Figure A.5 after you start SQL Server using the /m switch.





TIP: If you get an error message that indicates the log can't be opened, it may be that SQL Server is still running as a regular service. You have to stop it before you can start it in single-user mode.

After starting SQL Server, minimize the MS-DOS command screen--don't close it, or you stop SQL Server from running.

Figure A.5. The Command Prompt screen after starting SQL Server in single-user mode.

Redefining the Backup Device

When you start Enterprise Manager, you quickly notice that all of your databases, devices, and users have disappeared! This is because the rebuilt master has no idea what databases existed before its creation.

The first thing you need to do is redefine the backup device that stores the master database. Simply create a backup device that contains the path to the device used to back up the master database. (Re-creating a backup device won't erase the data in it; it just reestablishes the link.)

Restoring the master Database

After recreating the backup device, restore the master database in the same manner you would any database. After the master database is restored, SQL Server automatically stops (you should have a regular prompt back upon going to the MS-DOS command screen).

Restarting SQL Server Normally

Because you restored a copy of the master database that knew about your system, all of your databases and devices should reappear when you restart SQL Server. After restoring the master, SQL Server looks like it did in Figure A.1.

Restoring the msdb Database

Because rebuilding the master database also causes the msdb database to be rebuilt, you need to restore the msdb database from your last backups (you do have backups of it, don't you? ).

Restoring master without a Backup

If you have a corrupt or destroyed master database, but your user databases are still in good condition, there is a way to reconstruct the master database without using a backup. This is a last-resort method, as it requires some work on your part to make SQL Server exactly as it was before the crash.

Perform the following steps to reconstruct the master database without a valid backup:

Rebuilding the master Database

If you need to rebuild the master database, follow the same, previously outlined steps. Start the SQL Setup program and choose Rebuild Master Database (see Figure A.3). Make sure that you choose the same character set and sort order that you had before.

Run the Disk Reinit Command for Each Database Device

After rebuilding the master database, SQL Server resembles Figure A.4--the new master database knows nothing about any previous databases. The Disk Reinit command simply plugs the information about devices back into the master database and reestablishes the links between the two.

The Disk Reinit command resembles the Disk Init command from Day 3, "SQL Server Tools and Utilities," when you initially made the devices. The Disk Init command creates a new file and plugs the specifications into the master database, while the Disk Reinit command takes an existing file (not overwriting it) and plugs the specifications into the master database.

The Disk Reinit command needs to be performed for every database device (data and log devices). Backup devices can be recreated through Enterprise Manager in the same fashion that new backup devices are specified.

Running the Disk Refit Command to Reconstruct Your Databases

After the Disk Reinit command has been used, the Disk Refit command is issued; it forces SQL Server to scan all the database devices in order to reconstruct what databases are contained within them. The Disk Refit command needs to be performed immediately after issuing the Disk Reinit command, or it may fail.

Reconfiguring SQL Server to the Settings in Place Before the Crash

After rebuilding the master database, the new master is set to the installation defaults. You need to go to the Configuration tab (see Figure A.6) and enter all of the configurations that were present before the crash.

Figure A.6. The SQL Server Configuration screen.

Creating New Users to Replace Those Present Before the Crash

The SA account and the three system accounts (probe, repl_publisher, and repl_subscriber) are the only SQL Server logins present after rebuilding the master. All the other users have been lost. You need to reconstruct those users and put them back into their databases (see Day 6, "SQL Server Login and User Security"). Groups should still be in each individual database, but because users were linked to their SQL Server login, you have to recreate all of the users and put them in their databases and groups.

Summary

SQL Server stores all of its configuration information in the master database. Losing the master database causes SQL Server to lose its links with your user databases, even if the user databases are still good.

Restoring the master database from a valid backup takes a few more steps than restoring an ordinary user database, but once restored, all of your users, databases, and configurations should reappear.

If you don't have a valid backup of the master database, the Disk Reinit command takes existing user database devices and plugs them back into the master database. The Disk Refit command then scans the devices for databases. Although you can recover your databases, all of your users and configurations need to be reconstructed.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.