Master database is corrupt, instance won't start - what are my options?

Question

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

Answer 1

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.

Answer 2

I just wanted to add a possible problem & solution that I just ran into - I had a similar situation during a failed Cumulative Update (SQL2016 CU12) and messages in the event viewer and errlog where saying "Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.", however I eventually found that if I just reran the CU executable, it detected the upgrade status as "Incompletely installed" and allowed me to simply run the update again, after which it completed successfully and the master database and all others opened with no problems.

Answer 3

I also wanted to mention that I had the same issue, started down the path of recovering my master (not having a .bak file was slowing me down, even though I had backup copies of master.mdf and mastlog.ldf). When "sqlservr -m" failed, I checked the SQL error log, and found these errors,

Error: 2775, Severity: 17, State: 12.

The code page 65001 is not supported by the server

Searching for "error 2775", I found a thread in msdn, which led me to the ultimate solution, in the StackExchange DBA section,

Error starting SQL Server 2017 service. Error Code 3417

The short answer? An OS update to Windows 2019 Server somehow introduced a Regional settings change, which activated "Beta:UTF8 Support". By turning this setting off, and rebooting the server, SQL Server was back up and running.

This may not be a fix for everyone, but if the SQL Server service will not start, after you update Windows, it is simple to check for this setting being the cause.