One of the most tricky and sensitive tasks a DBA encounters is recovering the master database.
This task is also one of those DBA tasks which you do not get to hopefully practice often.
If the master database has problems, then every database on the server will have problems.
This article will cover the scenario that is typical when master database has problems.
Corrupt or Bad Master
One of the most difficult situations to resolve is a corrupt or
bad master database. To restore a master database in this situation, you could:
- Rebuild the master database. In SQL Server 6.5, you will have to run setup in the SQL Server program file group. In 7.0 run Rebuildm.exe in the \Mssql7\Binn directory.
- Place SQL Server in single user mode. This can be done by starting SQL Server
with the -m parameter.
- Restore your master database from the last known good backup.
- Verify that the master database was successful restored.
- Confirm that all the databases are up an running.
- Restore the MSDB database from the last known good backup.
- Stop and start SQL Server in normal mode.
- Open your database for production users.
If this method fails, can in 6.5 run a DISK REINIT after rebuilding the master:
DISK REINIT
NAME = 'logical_name',
PHYSNAME = 'physical_name',
VDEVNO = virtual_device_number,
SIZE = number_of_2K_blocks
[, VSTART = virtual_address]
In SQL Server 7.0 or 2000, you can run sp_attach_db:
sp_attach_db [@dbname =] 'dbname',
[@filename1 =] 'filename_n' [,...16]
This will work most of the time. If this does not work, you will have to rebuild the devices and databases and then restore the databases individually.
As you can see, one must make backups of the master database often to ensure a stable server. You will also want to generate scripts of your databases and devices regularly so
if needed, you can reverse engineer your databases.
