SQL SERVER – Script level upgrade for database ‘master’ failed – CREATE SCHEMA failed due to previous errors

SQL Server troubleshooting topics seem to be the most searched for in this blog. In continuation to this, blog for today is also inspired with one such error message. Some time back I have written below blog which talks about upgrade script mode of SQL Server:

SQL SERVER – Login Failed For User – Reason Server is in Script Upgrade Mode

One of the reader reached out to me with below error in ERRORLOG

Setting object permissions…
Error: 2714, Severity: 16, State: 6.
There is already an object named ‘TargetServersRole’ in the database.
Error: 2759, Severity: 16, State: 0.
CREATE SCHEMA failed due to previous errors.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 2714, state 6, 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.

Above error is truly scary. “Restore master from a full backup, repair it, or rebuild it”. So I asked my friend to share the script ‘sqlagent100_msdb_upgrade.sql’ to check which query is failing. It was not very difficult to find (I searched for “Setting object permissions…” which was printed in ERRORLOG before error) and I found that below is the piece present in script, which was failing.

IF (EXISTS (SELECT *
FROM msdb.dbo.sysusers
WHERE (name = N'TargetServersRole')
AND (issqlrole = 1)))
BEGIN
-- If there are no members in the role, then drop and re-create it
IF ((SELECT COUNT(*)
FROM msdb.dbo.sysusers   su,
msdb.dbo.sysmembers sm
WHERE (su.uid = sm.groupuid)
AND (su.name = N'TargetServersRole')
AND (su.issqlrole = 1)) = 0)
BEGIN
EXECUTE msdb.dbo.sp_droprole @rolename = N'TargetServersRole'
EXECUTE msdb.dbo.sp_addrole @rolename = N'TargetServersRole'
END
END
ELSE
EXECUTE msdb.dbo.sp_addrole @rolename = N'TargetServersRole'

To be very precise, adding the role was failing on MSDB database on his SQL Instance. I asked him to run below and got an error.

Now, Since SQL we were not able to connect to SQL, we need to use trace flag 902 to bypass the script. Here are the steps followed.

1. Applied the trace flag -T902 on SQL Server configuration manager
2. Start SQL Services and it should allow us to connect because the trace flag would bypass upgrade script mode.
3. Find the schema/role TargetServersRole in MSDB and make a note of members, if any.
4. In his case, the schema was having someone else as owner. Asked him to modify using below T-SQL
USE [msdb] GO
ALTER AUTHORIZATION ON ROLE::[TargetServersRole] TO [TargetServersRole] GO

5. After fixing the cause, we stopped SQL, removed trace flag and started again.
6. This time script got executed and SQL was out of upgrade mode.

Have you also encountered similar issue?

Reference: Pinal Dave (https://blog.sqlauthority.com)