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 Mssql7Binn 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.
DISK REINITNAME = ‘logical name’PHYSNAME = ‘physical name’VDEVNO = virtual device numberSIZE = 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.
########################################################
This tip has been originally published on Microsoft Italia’s web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli
########################################################
