Binaryworld - Restore the SQL Server master database ... [ SQL Server -> Backup-Restore ]

Restoring a corrupted master database is one of the most difficult tasks you may have to deal with. If this catastrophic event happens, here's how to proceed.

First, you need to rebuild the master database. Details on this operation depend on the SQL Server version you run. With SQL Server 6.5 the only way is to re-install the program, whereas SQL Server 7.0 provides an utility called Rebuildm.exe, in the \Mssql7\Binn directory.

Set SQL Server on single-user-mode, by starting it with the -m switch. Next, restore the master database from the latest avaible backup. At the end of the restore procedure, after verifying that all databases are working correctly, you can proceed restoring the MSDB database. When this step completes, stop SQL Server and start it again in normal mode. After the restart remember to check that all the user databases are working properly. If they aren't, the action to undertake depends on the SQL Server version.

With SQL Server 6.5, perform a DISK REINIT, which restores the data about the system tables usage in case of an existent device but missing data in the sysusages table.

Click here to copy the following block

DISK REINIT
NAME = 'logical name'
PHYSNAME = 'physical name'
VDEVNO = virtual device number
SIZE = number of 2K blocks
[, VSTART = 'virtual address']

SQL Server 7.0 doesn't support DISK REINIT any longer, but the same operation is available through the sp_attach_db system stored procedure, which connect a database to a server:
sp_attach_db [@dbname =] 'database name', [@Filename1 =] 'File 1 name' [, ... 16].

If this sequence of operations can't solve the problem, you'll need to rebuild each device and database and then perform the individual restores. The importance of regular backups, and in particular the master database master, can't be overemphasized enough, as it can assure the server stability and a quick restart in case of problems.