Sql Server 2012 - Cannot recover the master database

Question

Hi all,

I installed SS2012 Dev a couple of months ago on my dev machine(Win 7) - it worked for a while, but now it won't start and there are two errors in the Event log.

Error 1

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

Error 2

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

I tried a repair install, but that failed.

My dev machine also has SS2008 Dev on it as well -- which is working. I don't have a master backup as this is a dev machine and there is no critical data involved.

Anyone know where to go from here?

Regards

Melt

Answer 1

Please provide us the error log details ...

----------------------------

Thanks,Suhas Vallala

Answer 2

PLease share your SQL server error log & erro got during repair.

1) Try to rebuild master database

2) You can replace your master database files with database files of you running dev server. It may cause some config issue. PLease ensure server is not having any critical data & version of both instances are same.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

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

You don't have to rebuild or reinstall or repair SQL when you see these kind of errors. Normally when you upgrade SQL or when you apply some patches, when the SQL instance restarts it runs few SQL scripts on the system databases. Sometimes due to some kind of error the script upgrade fails and you will see his kind of error in the SQL logs and the SQL service will not start.,

Just above this error, you will find the actual error causing the script upgrade to fail, so you need to fix that error so that the script upgrade can be completed next time without any issues.

You can skip the scrip upgrade using trace flag -T902 and then you will be able to start SQL and connect to it to fix any kind of issues caused the script upgrade to fail

Answer 4

It looks like you master database has been corrupt. I suggest you to rebuild the master database. I have wrote the following article that shows how you can do that:

http://msdn.microsoft.com/en-us/library/dd207003.aspx

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 5

Thanks guys for the replies, it seems this is a windows update error.

Thanks V Keerthi Deep, your answer is correct.

The solution is to start the server with Trace Flag T902 enabled, delete the rows in the msdb table 'dbo.syspolicy_policy_execution_history_internal', shut down, turn off  the 902 Trace Flag and restart as normal -- problem solved.

Regards
Melt

Answer 6

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

You don't have to rebuild or reinstall or repair SQL when you see these kind of errors. Normally when you upgrade SQL or when you apply some patches, when the SQL instance restarts it runs few SQL scripts on the system databases. Sometimes due to some kind of error the script upgrade fails and you will see his kind of error in the SQL logs and the SQL service will not start.,

Just above this error, you will find the actual error causing the script upgrade to fail, so you need to fix that error so that the script upgrade can be completed next time without any issues.

You can skip the scrip upgrade using trace flag -T902 and then you will be able to start SQL and connect to it to fix any kind of issues caused the script upgrade to fail

An another trouble like this occurs after I upgraded SQL Server 2012 with SP1, but is related with Filestream feacture.

I solved my problema after starts with -T3601 and get in the log the Filestream trouble:

PRINT '-----------------------------------------'
PRINT 'Starting execution of PRE_SQLAGENT100.SQL'
PRINT '-----------------------------------------'

use msdb
 
2013-03-19 10:14:27.30 spid5s      -----------------------------------------
2013-03-19 10:14:27.30 spid5s      Starting execution of PRE_SQLAGENT100.SQL
2013-03-19 10:14:27.30 spid5s      -----------------------------------------
2013-03-19 10:14:27.30 spid5s      Executing batch :
 -- Check that we're in msdb
IF (DB_NAME() <> N'msdb')
  RAISERROR('A problem was encountered accessing msdb. upgrade script terminating.', 20, 127) WITH LOG
 
2013-03-19 10:14:27.30 spid5s      Executing batch :
 
CHECKPOINT
 
2013-03-19 10:14:27.32 spid5s      Executing batch :
 
--set compatibily level to 110
ALTER DATABASE msdb
  SET COMPATIBILITY_LEVEL = 110
 
2013-03-19 10:14:27.32 spid5s      Setting database option COMPATIBILITY_LEVEL to 110 for database 'msdb'.
2013-03-19 10:14:27.33 spid5s      Executing batch :
 
-- Allow updates to system catalogs so that we can fully manipulate our system objects
EXECUTE master.dbo.sp_configure N'allow updates', 1
 
2013-03-19 10:14:27.36 spid5s      Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
2013-03-19 10:14:27.36 spid5s      Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
2013-03-19 10:14:27.36 spid5s      Executing batch :
 RECONFIGURE WITH OVERRIDE
 
<{81E6B8A0-60E9-4A9F-82E3-FD5326CC3663}>RsFxFt.Dll::RsFxMgmtInitialize failed: Error 0x80070002 (-2147024894)
<{C580416B-A13E-4ECD-B61B-AAFAE39E5E35}>Failed to initialize the CFsaShareFilter interface
<{1038F43D-3391-45F7-B1B3-BADF26459429}>Failed to initialize CFsaShareFilter: Error 0x80070002 (-2147024894)
2013-03-19 10:14:27.36 spid5s      Error: 5597, Severity: 16, State: 1.
2013-03-19 10:14:27.36 spid5s      FILESTREAM feature could not be initialized. The Windows Administrator must enable FILESTREAM on the instance using Configuration Manager before enabling through sp_configure.
2013-03-19 10:14:27.36 spid5s      Error: 912, Severity: 21, State: 2.
2013-03-19 10:14:27.36 spid5s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 5597, 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.
2013-03-19 10:14:27.37 spid5s      Error: 3417, Severity: 21, State: 3.
2013-03-19 10:14:27.37 spid5s      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.
2013-03-19 10:14:27.37 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

I've disabled Filestream feacture in SQL Server Configuration Manager and Restart SQL Server Service.

Now I can't use Filestream receive error:

"There is an unknow error applying the FILESTREAM settings. Check de parameter valid. (0x80070002)"

Anyway, mY SQL Server instance is running fine without FILESTREAM.

---