Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore Master Database


Restore Master Database

Author
Message
dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 1958
I'm testing what to do in scenario where the Master database is corrupted beyond the ability for SQL Service to launch or the Master DB drive is lost. In this scenario we do have system database backups but you need to have the instance running to restore backups, and to do that, you need Master db that allows the instance to start.

The SQL version is 2008 R2 SP2. I stopped the service and renamed master.dbf to master.old. I tried starting SQL service and got an error as expected.

Based on my reading I did the following:

1) Open a command line window and go to
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

2) Run this command:
setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance name> /SQLSYSADMINACCOUNTS=<account> /SAPWD=<sa password.

This ends with an error, with the following from summary.txt:
Exception summary:
The following is an exception stack listing the exceptions in outermost to innermost order Inner exceptions are being indented

Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException
Message:
The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.

I found one thread where someone thought this was dues tot he instance initially installed as EVAL version sunsequently being upgraded to full liscense. This is also true of the instance I am using. What else might it be?
dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 1958
I finally figured it out. The is one of those things Bill Gates once described as a "puzzle you're supposed to figure out." BOL doesn't give you enough information to pull it off, and the process for SQL 2005 and 2008 described several blog posts doesn't work in 2008 R2.

This is what works when the master database in SQL 2008 R2 is lost or too corrupted to allow SQL Service to start. This assumes you have backups of the system databases. If you're not making these for all your instances, put that at the top of your to-do list.

1) Open a command prompt using the AS Administrator option and navagate to
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\
(This is a different folder than for SQL 2008 - that one just gives you cryptic errors on step 2 below)

2) Run setup.exe /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<current admin account>
Use your instance name instead of MSSQLSERVER if repairing a named instance. Add /SAPWD=<sa password> if using Mixed Mode, and /SQLCOLLATION=<collation> if not using the default (SQL_Latin1_General_CP1_CS_AS>. This will rebuild the system databases from templates.

3) In SQL Configuration Manager, add the single user mode switch -m; to the Start Parameters do not leave a space before the other parameters. Launch the Instance. This will launch SQL Server in single user mode.

4) Run SQLCMD in the command window. Enter the following
1>restore database master from disk = '<path to master backup file>' with replace
2>go

When the master database is restored, SQL service will shut itself off.

5) Go back to SQL Configuration Manager. Remove the Single User Mode switch -m; from the Startup Parameters. Start the SQL Server Instance.

6) Launch SQL Management Studio and restore the MSDB database from your backup file. This will restore all your SQL Agent jobs. If you have modified the Model database, restore that one from backup too.

Later I'll check SQL 2012 BOL to see if the procedure for 2012 is documented there. I'm out of here for the day...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
I discussed that and other problems in this article

http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
As the OP has found and Gails blog shows, recovering from system database corruptions can be a complex and error prone process, especially for the model database. Microsoft and BOL are woefully negligent in documenting the process.

AS Gail also alludes to in her Blog an alternative is to take flat file copies of the system databases after installation, I would URGE everybody to do this. By all means know the documented process for doing this, but give yourself the ability to replace a stressful multi step process with a simple option of sliding a replacement file into place.

I copy the system database files (including resource) after every patch so I have version correct copies and then make sure they are offsited with other backups.

This is not an alternative to regular backups of the system databases as obviously the one off file copies will become out of date as logins, jobs etc are added, so do that as well, but the file copies will get your system backup and running much quicker.

---------------------------------------------------------------------
dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 1958
My thought was that once your server config, databases and security is set up, take a short downtime to copy all the system databases to a different drive. If something bad happens, you may be able to drop them back in much more easily than the process I described. Does the Master database change daily, or just when you add/drop DBs, make config & security changes, etc?
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
master will change when you make server level changes, so as you said when you change security, configurations or changes to other databases such as add or drop or move files. MSDB will change more often as it keeps backup\restore and job\SSIS history. This is why you also need to back them up. Model would change rarely if at all.

Its good enough to make file level copies on install and with every SQL patch.

---------------------------------------------------------------------
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
The point of copying the files is not for a backup, that's what regular, scheduled database backups are for. The file copies are just so that you have files to use should the system databases disappear completely. It doesn't matter if they're out of date, you're just using the file copies so that you can start the instance and then restore the latest backup.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 585
We found that the hard way and I wish we had seen the blog prior. We resolved it with a like system doing exactly what the blog shows.

.
Markus
Markus
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1582 Visits: 3672
I agree. I always stop SQL Server and make file copies of master, model, msdb after an install and prior and after a Service pack too. As a just in case.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search