1/30/2012

Rebuild System Databases

Yesterday night one of FB Friend have ping me, he has issue with his local SQL Server instance. Our conversation goes like:
FB Friend: Hi
I : Hi
FB Friend: I need your help
FB Friend : My SQL Server is not starting...
I : What is an error you are getting?
FB Friend : Just nothing
I : Ok, check the SQL Server Error Log and EventViewer
I : you can find errologs at C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\Log
I : open it with notepad ....
FB Friend: Ok
FB Friend: Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
I : Wow, seems like your master database is corrupt, you will need to run repair or you may want to rebuild master database.
I : Is this a new installation ?
FB Friend : Yes, this is new installation, and, I don't want to re-run the installation again, what is my option ?
I : you will need to rebuild master or restore it
FB Friend: How do I do it?
I: Here are your steps

Rebuild Master:

Step 1: Insert your setup media CD or folder where you have dumped SQL Server Installation
Step 2: Open command prompt
Step 3: Go to location (wherever you have setup, Step 1)
Step 4: setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=MyAdmin /SAPWD=somePwd

Step 5: this will pop up new command window and then disappears

Step 6: you will have to refer logs so as to see whether it was successful completed or exit with error

This is it, you system database has been rebuild successfully!!
Restoring Master Database - An alternative method:
Step 1: Take backup of master database, in your case, I assume, you already have your master database backed up.

Step 2: Stop SQL Server Services * you will need Administrator permission for this operation

Step 3: Start SQL Server using -C and -M parameter; this will start SQL Server in single user mode which is required to restore Master database

Step 4: Restore database using SQLCMD

This is it, you have successfully restore your master database!!!

Is it this simple? No, wait, there are few things we need to be careful with....

The above example have save you because below conditions are true:
  1. You have back up of your master database, or
  2. This server is fresh installation or
  3. there are no user databases or
  4. this server is not production box
If the above conditions were not true, you will have to perform below steps:
  1. Restore / Rebuild system databases
  2. Recreate all the Login that you have on your crashed server - backing up master db helps you here
  3. you will have to recreate all the jobs and SSIS/DTS Packages that you have - backing up MSDB database will save you
  4. similarly distribution database backup will help you if you have replication configured
Conclusion :  I wish that you have schedule maintenance plan / job to take full backup of your system databases i.e. Master, MSDB, and Distribution(if you have configured replication).
Disclaimer : The example used here is for illustration purpose only.

2 comments:

  1. When I perform a new SQL server installation, one thing I do once installed is stop SQL server services, make a folder called "Orginal Files" and copy the .MDF and .LDF of all the system database to this folder. Once completed I restart SQL server. This allow me to fix any database issues beefore I load any data/databases. If there is a problem I can just start back from the begin. But I alway ensure that I have backups of all user and system databases too.

    ReplyDelete
  2. That's nice. Thanks for the sharing. However, if you are aware there would template for system databases at \MSSQL\Binn\Template that can be used to!!

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri