Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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:
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.
Total article views: 8801 | Views in the last 30 days: 7
 
Related Articles
FORUM

How to restore master database.?

How to restore master database.?

FORUM

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

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

ARTICLE

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 ...

BLOG

Restore Master From Backup

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

FORUM

database backup and restore in sql server 2005

database backup and restore using java

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones