8

Help! My master database is corrupt, I can't even bring the SQL instance online! What are my options to get my server back up?

I do have a backup of master, but the MSDN page "Restoring the master Database" asks me to start the instance in single-user mode, which I can't do!

(Note: I'm leaving this question unspecified as to SQL version, so as to be a more widely-applicable reference. There are some similar questions on DBA.SE, but none that involve the server unable to start.)

  • (Comments or other answers are certainly welcome, but I asked this in an attempt to provide a comprehensive answer, including some aspects I couldn't find elsewhere on the web.) – BradC Feb 14 '17 at 17:36
9

Here are a few avenues I would investigate. Don't do all of these (some of them are different techniques to accomplish the same purpose), but are worth considering:

1. Examine the SQL Error Log directly

Browse directly to the folder containing the SQL error logs and load the most recent ERRORLOG into notepad to get more detail on why the SQL instance will not start. Perhaps you will find the problem isn't with the master database at all.

2. Try to start the instance in single-user mode

Here is a full list of startup options for SQL server, including -m (single user mode) and -f (minimal configuration mode). Other options allow you to specify the path for the master database, if that is the problem.

If you are able to get the instance started, follow the steps in the MSDN article you linked for restoring the master database, or this detailed walkthrough by Thomas LaRock.

If another application always grabs the single user connection before you can, first disable the SQL Agent so it isn't starting. Second, see the ideas on this question for using the -m"Application Name" parameter to specify the application name.

3. Restore master to another instance and copy its files

I've only found one other mention of this undocumented technique, but I used it successfully this past weekend, so it might be worth a try.

If you can't start the instance in single-user mode, but you have another SQL instance running the exact same release and build, try restoring the last known good master database backup from your dead server to the other instance:

  • Restore as a different name, of course (master_please_god_let_this_work), WITH MOVE so you don't overwrite master on your good server
  • Restore WITH NORECOVERY. Not sure this is necessary, but made me feel better that I knew the other server wasn't going to alter anything in the restored master
  • Set it to offline: ALTER DATABASE [master_please_god_let_this_work] SET OFFLINE
  • Copy the restored MDF and LDF files from the good server to the dead server
  • Rename the master.mdf and mastlog.ldf files as necessary to replace the bad master files with your restored versions
  • Cross your fingers and start the instance
  • Optional: do a fresh restore of master on the revived server. Not sure this is necessary, since we were pretty careful not to change master.

4. Rebuild the system databases

If you don't have another instance running the same version, or if you're not comfortable with using the undocumented procedure listed in #3, or if you don't have backups of master (why do you not have backups??), you can rebuild the SQL system databases from the original install disk:

Setup.exe /ACTION=REBUILDDATABASE /...

When this is complete, you can follow the steps linked previously to restore master from your last good backup. You will also need to restore a recent backup of msdb to keep all your jobs, job schedule, and job history.

5. Restore all USER databases to a new (or existing) SQL instance

If you've got another existing instance already running (proper SQL version, enough disk space), I would probably start database restores from the most recent backups while I'm working on the other troubleshooting steps above, just in case I need them.

If your new (or reinstalled) instance has access to the same disk, it is much faster to simply attach them as new databases:

CREATE DATABASE foo 
ON (FILENAME = 'D:\data\foo.mdf'),
   (FILENAME = 'D:\data\foo_log.ldf')
FOR ATTACH;

6. Re-do any changes to master

Once you successfully restore master (via any of the above techniques), you need to investigate any changes that might have been lost, if they were made after the backup you just restored:

  • Security changes
  • New databases (the files will still be on disk, just attach them)
  • Server-wide settings

There is no magic way to find these, you'll have to go back to your own company's documentation trail for these kinds of changes, if you have one.

  • 1
    Nice Post. +1. I made a test corrupting my master, restoring a backup on another server, and then copying the files to the old server. It worked with no problems. By the way, I got the error 3411 while the master was corrupted. – Racer SQL Feb 14 '17 at 17:41
  • Great, thanks for verifying that technique. In my case, I never got an actual error, but my master db was taking hours to recover, far beyond any possible cluster service timeout settings. – BradC Feb 14 '17 at 17:45
  • 1
    @RafaelPiccinelli The master keeps the "meta-data" about everything on the server (security, other databases, etc), so that makes sense. See my points #5 and #6. You'll have to re-attach those dbs and re-setup any security you had in place. BTW, I hope you're doing this all in a lab environment!! – BradC Feb 14 '17 at 18:19
  • Yes, that's why the test. I'm getting an error but probably is with permission in my storage. again, nice post. I will just delete this last comment haha. – Racer SQL Feb 14 '17 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.