Saturday, October 29, 2011

0 Restore database (3) Restore/Rebuild the master database

Restore the master database from previous backup

  1. -- Backup the master database
  2.   BACKUP DATABASE [master] TODISK ='C:\dump\master_20111029.db' with format
  3. -- Find the master database file
  4. select physical_name from sys.database_files
  5. -- shutdown sql server
  6. SHUTDOWN WITH NOWAIT

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

image

Connect to SQL server and restore the master db from previous backup. Upon the restore competition. The SQL server will shut down.

  1. -- restore the sql server master db from backup
  2. RESTORE DATABASE master from DISK ='C:\dump\master_20111029.db' WITH REPLACE;

image

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

image

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

image

As this post discuss http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/29a7f20b-a2a1-4e72-8eca-17e2037c1ae5/

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…..

image

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.

  1.   RESTORE DATABASE [master] FROMDISK ='C:\dump\master_20111029.db' WITH REPLACE;
  2. go
  3. -- Restore model and msdb
  4.   RESTORE DATABASE [model] FROMDISK ='C:\dump\model_20111029.db' WITH REPLACE;
  5. go
  6.   RESTORE DATABASE [msdb] FROMDISK ='C:\dump\msdb_20111029.db' WITH REPLACE;

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~~!!

Reference

Rebuild the system database http://msdn.microsoft.com/en-us/library/dd207003.aspx

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates