Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore Master Database Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 12:00 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 553, Visits: 1,618
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?
Post #1437039
Posted Friday, March 29, 2013 7:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 553, Visits: 1,618
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...
Post #1437170
Posted Saturday, March 30, 2013 3:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1437190
Posted Saturday, March 30, 2013 5:58 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,991, Visits: 12,939
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.


---------------------------------------------------------------------

Post #1437196
Posted Monday, April 1, 2013 10:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 553, Visits: 1,618
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?

Post #1437511
Posted Monday, April 1, 2013 10:21 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,991, Visits: 12,939
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.


---------------------------------------------------------------------

Post #1437515
Posted Monday, April 1, 2013 10:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1437519
Posted Monday, April 1, 2013 11:51 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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.

.
Post #1437553
Posted Tuesday, April 2, 2013 11:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:56 AM
Points: 1,272, Visits: 2,794
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.


Post #1438028
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse