Sql-server – Failure during install of SQL Server 2016 sp2

installationservice-packsql serversql-server-2016upgrade

I'm trying to install the new SP2 for SQL Server 2016, but it fails during the install with an Engine error.
This is what I can read in the errorlog>

Error: 468, Severity: 16, State: 9.
Cannot resolve the collation
conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
The failed
batch of t-sql statements :
CREATE PROCEDURE
sp_sqlagent_get_perf_counters @all_counters BIT = 0 AS BEGIN …

Error: 912, Severity: 21, State: 2.

Script level upgrade for database 'master' failed because upgrade step
'msdb110_upgrade.sql' encountered error 200, state 7, severity 25.
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.

Error: 3417, Severity: 21, State: 3.

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.

SQL Server shutdown has been initiated

The master database was running "Latin1_General_CI_AS" and MSDB (and other dbs) was running "SQL_Latin1_General_CP1_CI_AS".

I have new backups of all databases on the server including master and msdb.

I'm tempted to try to restore the master master as shown in https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql?view=sql-server-2017

But I'm trying ensure that it will actually work, and not just make the situation worse.

How do I get my SQL Server back up and running?

EDIT:
I've started the mssqlserver service like this:

NET START MSSQLSERVER /T902

This gets the server started, and I've looked into msdb110_upgrade.sql, and found quite a few missing "COLLATE DATABASE_DEFAULT"s which I've added.

But it is not taking my changes.

Best Answer

I've seen this error on other versions of SQL Server on a legacy build.

In my case, it was caused by the owner of a role or schema was not the default: Something like db_datareader was authorised by a user account (they no longer have sysadmin)

How to recover?

  • Could not restore master because the master database version was lower than the binaries version
  • Created a new install with the correct version to get it running, then restored master to that, then copied master mdf back to the original install.

Unfortunately, I can't remember the exact details now.
It's the only time in 20 years of SQL Server I've...

  • seen a service pack install fail that stops SQL Server running
  • had to deal with a version mismatch on restore of master
Related Question