HOW TO: Restore the Master Database in SQL Server 2016

I like having a routine. Most people do, I suppose. There’s some comfort in knowing what lies ahead, what comes next. This is also true for when you need to restore the master database in SQL Server 2016.

The same holds true for restoring the master database in SQL Server 2016, because it’s the same routine as what I posted for SQL Server 2014, which is the same routine as what I posted for SQL Server 2012. If you only click on one link, click on the 2012 link because that has the details on how to properly test that the restore worked.

For those of you too lazy to click on the links, I will summarize the steps for you here:

1. Using SQL Configuration manager, stop the SQL Server instance
2. Open a command window or Powershell session
3. In that command window, start the instance executable in maintenance mode using a secret command known only to people that have read my posts
4. Open a second command window or Powershell session and connect to the server instance using SQLCMD
5. Restore master from within that SQLCMD window
6. Using SQL configuration manager, restart instance

One thing to note here is that the instructions I provided assume you will be opening your command line sessions with ‘Run as Administrator’. If you don’t do that, bad things may happen. What bad things? This bad thing:

restore master database

“Your SQL Server installation is either corrupt or has been tapered with (Error getting instance ID from name.).  Please uninstall then re-run setup to correct this problem”

Or this bad thing:

restore master database

“RegOpenKeyEx of “Software\Microsoft SQL Server\MSSQL13.SQL2016\MSSQLServer\HADR” failed.”

My first thought when seeing this was trying to figure out what had gone wrong with the installation. I spent about seven (7) seconds thinking about the possible installation issues and then started thinking about user permissions for the command window. Once I switched to running the command window or Powershell session as an administrator, all was good.

That’s all there is to it, again. While SQL Server 2016 has been making a lot of headlines with all of the shiny new things inside, the steps to restore the master database has not changed.

You’re welcome.

3 thoughts on “HOW TO: Restore the Master Database in SQL Server 2016”

  1. “a secret command known only to people that have read my posts”
    I am so glad I’m one of those people – ’cause spelling counts 🙂

    Reply
  2. If the master database is well and truly hosed, can you even start the instance in maintenance mode? Maintenance mode still tries to load MASTER, correct?

    I had an instance with a weirdly bloated and corrupted master db that wouldn’t recover in any reasonable allowed time. I didn’t figure that “single user mode” or “maintenance mode” would make any difference, so I improvised and used a method that I see that Steve Harris mentioned in the SQL 2012 comments: restore a good backup of master to a different instance with a different name and NORECOVERY, then set the db offline, and copy/rename the MDF and LDF files to the hosed server.

    It worked.

    I think “rebuild the system dbs from the original install disk” method, as also mentioned by another commenter, would have been my next try: https://technet.microsoft.com/en-us/library/dd207003.aspx

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.