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.

Solarwinds

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.

SQL SERVER - Script level upgrade for database 'master' failed - CREATE SCHEMA failed due to previous errors TS-Role-01

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)

Solarwinds
Previous Post
SQL SERVER – Taking Backup Without Consuming Disk Space
Next Post
SQL SERVER – What is Semantics Search Inside SQL Server?

Related Posts

2 Comments. Leave new

  • Yes had this exact issue. There are 2 database roles called DatabaseMailRole and TargetServerRole in msdn. I had to script them out and delete them, then the install worked. It’s fine to remove them as the script re creates them.

    Reply
  • Thanks, very usefull!!!!!!

    Reply

Leave a Reply

Menu