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

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

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.

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...