Sql-server – SQL Server Service can not start up

sql-server-2012

I having the following issues, anyone has faced this ‘msdb110_upgrade.sql’ encountered error 2627, state 1, severity:

Cannot recover the master database. SQL Server is unable to run.
Restore master from a full backup, repair it, or rebuild it. For more
information about how to rebuild the master database, see SQL Server
Books Online.

Script level upgrade for database ‘master’ failed because upgrade step
‘msdb110_upgrade.sql’ encountered error 2627, state 1, severity 14.
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.

Best Answer

After an upgrade /patch of your SQL Server, the databases usually the system databases undergo an upgrade as well via scripts. If the upgrade encountered any issues, it is possible that you could corrupt or script upgrade would not be successful.

Couple of things you can do:

  1. Perform a repair of your SQL Server since you are using SQL Server 2012
  2. SQL Server 2012 does come with template system databases when you first install.

C:\Program Files\Microsoft SQL Server\MSSQL11.[instance_name]\MSSQL\Binn\Templates

This is the location where the system databases are stored. Remember these are templates from your first time installing so it will not have any custom configurations, logins, jobs, and more. You will have to copy it to the location of your corrupted system database and replace it.

  1. Option 3 is the use of a trace flag 902 which will bypass the script upgrade during SQL Server startup. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

WARNING: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your SQL Server instance.

Related Question