Restore the master database from previous backup
Start up the SQL server from command line as single user mode (-m)
“C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Binn\sqlservr.exe" -sMSSQL2008R2 -m
Connect to SQL server and restore the master db from previous backup. Upon the restore competition. The SQL server will shut down.
Rebuild the master database
In the event when the master db corruption, we will need to rebuild the master db from scratch and restore the master db from previous backup.
The procedure will not just only rebuild the master db, but also the msdb and model. you can not just rebuild the master. Make sure you have the most current backup for all the system db before you tried.
To simulate the master db corruption, lets rename master.mdf to master_old.mdf
When start up the sql server, it gives up the error as below
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQL2008R2 /SQLSYSADMINACCOUNTS=SG11\administrator
The detail report will be log under C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\summary.txt
After following up MSFT suggestion, I hit the error below
We need to run the setup from the installation media
E:\MSSQL2008R2\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQL2008R2 /SQLSYSADMINACCOUNTS=SG11\administrator
If your sql server set up as mix authentication mode, you must also provide /SAPWD, otherwise you will find the error message “ Configuration error description: Missing sa account password. The sa account password is required for SQL Authentication Mode.” in the summary log at C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\[TIMESTAMPE]\SUMMARY…..
After master being restore, restart SQL server to see whether it can be recover. Once it is confirmed, restart it again with single user mode, restore master from the last backup. Then restore msdb and model from backup.
As alternative method, Quantum John suggest here http://blogs.technet.com/b/fort_sql/archive/2011/02/01/the-easiest-way-to-rebuild-the-sql-server-master-database.aspx is that instead of rebuild the master db. restore the master db backup to the test server for different name (ex:restore_master) using WITH MOVE. Then detach the db and copy the db file back to the corrupt server. rename the mdf and ldf file. Bonce the corrupt server.
The version of the database server must be the same. Only major version matter, such as 2005 and 2008. Hotfix and service pack only exist in the resource db.
Although, it sounds very promising and easy, I have not try it myself. Something interesting to try it~~!!
Rebuild the system database http://msdn.microsoft.com/en-us/library/dd207003.aspx