Restoring the System Databases

The system databases have their own special considerations when it comes to recovery. If SQL Server can be started, they can be restored from backup like any other database. However, if the master database is damaged, you will first have to rebuild the system databases. In theory (and according to BOL), to do this, run the rebuildm.exe command prompt utility to re-create the system databases, returning them to the state they were in when SQL Server was installed. Now you start SQL Server in single user mode. You will notice that none of your user databases, SQL Server logins, or SQL Server Agent jobs, alerts, and operators are available. Restore the system databases from a valid backup, and you will be able to access all your databases and objects once again. Well, that's the theory anyway. In reality it is a little more involved than that, so I will outline a step-by-step procedure that will get you back up and running as quickly as possible. Believe me, when your master database is trashed it is not the best time to discover that the restore procedure doesn't work. Don't ask me how I know this!

So, your instance has crashed, and you determine that it is due to a problem with the master database. Of course you have a backup, but you can't restore unless the instance is running, and you can't get the instance running without a valid master database. This is where the rebuildm.exe utility comes in. Before you run the utility, ensure that all SQL Server services are stopped. When you run rebuildm.exe the application prompts you for the server (instance) you are restoring, the location of the source files for the system databases, and the database collation settings, as shown in Figure 16.9.

Figure 16.9. The Rebuildm.exe interface.


It is here that you can start running into trouble. The location of the source files is defined as the original source of the SQL Server installation, usually the \X86\DATA folder on the installation CD-ROM. The files on the CD are, of course, read only and are copied as such to the data folder where the system databases were installed. After rebuildm.exe copies the files for the system databases to the data directory, it then pops up a Configuring Server screen with a progress bar; this will run forever and never succeed, as you can't modify a read-only database file. If this is the first time you've attempted this (likely) and you don't know how long it takes to run, you can waste an awful lot of time sitting waiting for a process that will never complete. I, for one, am usually hesitant about killing a process that claims to be rebuilding the master database. The fix to get around this is simple. Before you run rebuildm.exe, first copy the DATA folder from the CD to your hard drive. Now right-click the new DATA folder and clear the read-only check box and, when prompted, agree to have the change propagate to all files and subfolders. Now run rebuildm.exe and it will succeed in its reconfiguration of the system databases. This includes running scripts that update the databases to the proper Service Pack level. The process takes only a few minutes.

Now that the original system databases have been restored, you can get the instance started, and restore your backup of the master database. To restore the master database you must start the instance in single user mode. Don't even bother with Enterprise Manager at this point. You will be able to connect and start the instance using EM, however, if you go to the database properties of the master database, you will see that the single user mode option is grayed out. The proper way to start the instance in single user mode is using the -m option from the command prompt or using Control Panel/Services. I recommend using the command prompt, because if you enter a startup option in the services applet, you must remember to go back to it, remove the option, and restart the service or on the next server startup it will revert to single user mode. Here is the next potential pitfall in the restore process; when starting the instance from the command prompt, you must run sqlservr from the Binn folder of the instance you are restoring and specify the ?m option for single user mode as well as the ?s option to specify the instance name as shown here:

G:\MSSQL\SQL2000\Binn\sqlservr ?m ?s SQL2000 

Now you are ready to actually restore your master database. The restore process can be run from osql, Query Analyzer, or Enterprise Manager. If you use EM be aware that it will hang when the restore is complete as the restore of master terminates the instance when it finishes. Remember that at this point any backup devices you have previously created won't be available, so you will have to provide the path to the tape or disk where your last valid backup of master is located, as in the following:


Once the restore is complete, you can restart the instance normally, and you should be back in business. Keep in mind that when you ran rebuildm.exe it rebuilt all the system databases so you will now have to restore msdb, as well as model if you have made any changes to it.

I strongly suggest you run through the preceding procedure on a test server a few times, and create a master database recovery document that is specific to your site. The recovery of the master database is not all that complex once you know the tricks, but you should be comfortable with it before you actually have to perform it on a production server.

After you have rebuilt the system databases, and for some reason (I can't think of a good one) you don't have a valid backup of your master database, all is not lost. You can still gain access to all your user databases without having to restore each and every one of them as well. As long as it is only the master database that was damaged, and the other database files are still intact, you can use the system stored procedure sp_attach_db or sp_attach_single_file_db to inform the master database about your user databases. Think of it as reverse engineering the database information stored in master. The following example "attaches" the Northwind database:

EXEC sp_attach_single_file_db @dbname = 'northwind' @physname = 'c:\mssql\data\nwdata.mdf  '

This will be much faster (and potentially safer) than restoring the user databases. You will still have to re-create other items such as logins and configuration settings that are stored in the master database.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features