March 29, 2013 at 12:00 pm
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?
March 29, 2013 at 7:19 pm
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...
March 30, 2013 at 3:53 am
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
March 30, 2013 at 5:58 am
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.
---------------------------------------------------------------------
April 1, 2013 at 10:05 am
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?
April 1, 2013 at 10:21 am
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.
---------------------------------------------------------------------
April 1, 2013 at 10:26 am
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
April 1, 2013 at 11:51 am
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.
.
April 2, 2013 at 11:42 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply