SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Restoring the Master Database

By Brian Knight,

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:
  1. 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.
  2. Place SQL Server in single user mode. This can be done by starting SQL Server with the -m parameter.
  3. Restore your master database from the last known good backup.
  4. Verify that the master database was successful restored.
    1. Confirm that all the databases are up an running.
    2. Restore the MSDB database from the last known good backup.
  5. Stop and start SQL Server in normal mode.
  6. Open your database for production users.
If this method fails, can in 6.5 run a DISK REINIT after rebuilding the master:
	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.
Total article views: 8839 | Views in the last 30 days: 3
Related Articles

How to restore master database.?

How to restore master database.?


Error .........While Restoring MASTER database

Error .........While Restoring MASTER database


Database Master Key Error After Database Restore

When restoring a database to another server, you may get a master key error if using symmetric keys ...


Restore Master From Backup

There are a few posts running around talking about restoring master from backup. Thomas LaRock has a...


How to restore Master database – SQL Server System Database?

Steps to restore SQL Server Master Database :- 1) Stop SQL Server Services 2) Add [-m] startup p...