Yikes! Cannot recover the master database!

Recently, during patching for a 2019 SQL Server, the SQL services refused to start after the patching. It was throwing the following heart attack inducing error:

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.

This is not the first time I have seen this error. Actually, this seems to be the default error that Microsoft throws any time there is a problem with the patching process. The good news is, most likely this is a false error and the master database is perfectly fine.

First, Don’t Panic!

As I said, this error can be misleading. I wish Microsoft would change this to something a little less terrifying, like “An error occurred during the patching process, see log for details.” But, they didn’t ask me…

Next, Figure Out What Really Happened

In order to find out what’s really going on, you need to look at the SQL Server error log. One way to do this is browse directly to the folder containing the SQL error logs and try to open the error log in notepad to get more detail on why the SQL instance will not start. Or, you can try to bring SQL Server online and view the log in SSMS like you normally would.

Bring SQL Server Online

You need to try starting your SQL Service specifying Trace Flag 902 at startup. (Sometimes specifying the -m parameter to start in single user mode works too) If this works and SQL Server comes online, congratulations, your master database is just fine and you are just a few steps away from getting this problem fixed!

Now that you have SQL Server up and running (hopefully) look through the error log until you find the dreaded “Cannot Recover Master Database” error and continue to look through the log at the errors preceding that error. You will typically find something along these lines:

Script level upgrade for database ‘master’ failed because upgrade step ‘ISServer_upgrade.sql’ encountered error 15559, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

This means you are on the right track. In my experience, this typically means that an error occurred in one of the scripts the patching process was trying to run. This particular problem in the master database, but I have also seen it in SSISDB and MSDB as well. Keep looking through the error log, usually the root of the problem will be the error preceding that one. In my particular case, it was this:

Cannot drop asymmetric key ‘MS_SQLEnableSystemAssemblyLoadingKey’ because there is a login mapped to it.

Now we’re getting somewhere! SQL Server failed to start because the patching process was trying to drop an asymmetric key in Master, and it couldn’t because there was a login tied to it. No corrupt Master database, no need to restore, just a login that needs to be taken care of. In this case, there was a MS_SQLEnableSystemAssemblyLoadingUser login tied to the MS_SQLEnableSystemAssemblyLoadingKey key. I simply dropped the login and the key with the below script, and then SQL Server was able to start up normally and came back online with no further issues.

Your Error May Be A Little Different

As I said, I have seen this problem occur several times, and not always in the Master database. Whether it is Master, SSISDB, MSDB, or another system database, the fix is usually something as simple as removing (or adding) a login or key of some sort. The main thing is to not panic, and to figure out what the real error is.