SQL SERVER – The Log Scan Number Passed to Log Scan in Database ‘master’ is not Valid

If you have landed on this blog by searching below error, then I must tell you that your SQL Server service is not getting started. Let us learn in this blog how to fix  The Log Scan Number Passed to Log Scan in Database ‘master’ is not Valid.

2016-01-27 23:36:41.95 spid9s      Starting up database 'master'.
2016-01-27 23:36:42.04 spid9s      Error: 9003, Severity: 20, State: 6.
2016-01-27 23:36:42.04 spid9s      The log scan number (469:72:10) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
2016-01-27 23:36:42.04 spid9s      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.
2016-01-27 23:36:42.04 spid9s      SQL Server shutdown has been initiated

When I was working with my client, all they wanted was to get his SQL Server up and running as soon as possible. I always wish that client has a database backup before the problem. In this case, the master database was corrupt.

The interesting situation here:

• To restore master database, we need to start SQL Server.
• To start SQL Server, we need a master database, which is corrupted.

SOLUTION / WORKAROUND – Log Scan

to move forward, we had to rebuild the master database first. Here is the command which we should use.

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS= /SAPWD

Instance name needs to be replaced based on our installation. For a default instance, its MSSQLSERVER else it should be the instance name. We need to keep in mind that rebuild would rebuild other system databases, msdb and model, as well. It would be safe to take MDF and LDF files of those databases. They can be replaced back once rebuild is complete.

Once rebuild is complete, we need to restore the master database using the query below:

• Start SQL Server in single user mode with -m parameter.
• Use below T-SQL to restore master.

RESTORE DATABASE master FROM disk='C:\backup\master.bak' WITH REPLACE

After rebuilding and restore, we could bring the SQL Server resource online successfully.

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