Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Rebuild SQL Server Master Databases in Less than 5 Minutes

By Rudy Panigas,

Rebuild SQL Server Master Databases in Less than 5 Minutes

Have you ever has an issues with a SQL server (2000, 2005, 2008 and so on) that you needed to rebuilt the master database? I've had that problem not too long ago and had to spend some time researching how this can be done. Here is a good site with the information you will need to rebuild the master database (http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx). But with a little work you can rebuild the master and the other system databases in less than 5 minutes. Let's see how this can be done.

It's All Proactive

To quickly rebuild the master and system database you need to have done some proactive work before you need to do a rebuild. Here is a quick and easy list of task to do proactively.

  1. Stop all SQL server services
  2. Copy the master.mdf and master.ldf files from the SQL server's data folder to another folder
  3. Restart all SQL server services

Now you have performed all you proactive tasks. A backup of these files exists in their native format. This is not a backup done via SQL server but a command line level or file level backup.

Time to Rebuild

So now what? Now when a master database has to be rebuilt due to corruption of some kind you can quick get back to a good state by performing the following.

  1. Stop all SQL server services
  2. Rename the master.mdf to master.mdf.old and master.ldf to master.ldf.old
  3. Copy the master.mdf and master.ldf files from your folder to the SQL server's data folder
  4. Restart all SQL server services
  5. Log into SQL server to ensure you can connect
  6. Perform a restore of the master database from your backups

At this point your SQL server will start up as normal and you can then continue to perform your additional tasks as needed.

Real World Example

Here is my real world example. At a disaster recovery test I had to build a SQL server 2008 environment and restore all of the SQL server's databases. This included all user and system databases. After the initial installation of SQL server 2008 I copied the master database files to a new folder I called 'D:\OrgFileBackup'. Then I performed the restore of the master database from our tape backups. During the restore an error occurred and the master database became corrupted. I could no longer log into SQL server nor restore any database. My only choice was to rebuild the master database. I performed the steps above and was able to connect to SQL server and perform my restores.

If I had to rebuild the master database or worse uninstall SQL server and reinstall SQL server completely the restore time for the whole environment would have taken a lot longer.

Rebuild Completed

This process is not intended to replace your backups but to enhance it. Now that you can log into SQL server you can perform a restore of the master database. You must restore from backups to get all the latest information back into the master database.

This process will not cure everything, but it is a faster way to rebuild your master database.

Total article views: 11927 | Views in the last 30 days: 4
 
Related Articles
FORUM

How to restore master database.?

How to restore master database.?

FORUM

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

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

FORUM

Automated Master Database Rebuild

Need an automated procedure that can rebuild the master database using the Rebuildm.exe

ARTICLE

Restoring the Master Database

This article shows you in a step-by-step manner how to restore the 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 ...

 
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