About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Friday, June 10, 2011

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.

Hope you find this helpful,

Rudy
Posted by at

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)