The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog



No Ads ever, except search!
Saturday, July 20, 2024 Login

Blog posts for the month of June,2014.
Best cane this side of the Ukraine6/16/2014 1:37:30 PM

Here is Vasilli working the best cane this side of Ukraine.


Eagle Cane and walking stick

Cannot recover the master database on a clustered sql server instance6/6/2014 11:33:54 AM

Cannot recover the master database.
-------------------------- From SQL Server Error Log ---------------------
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.
During redoing of a logged operation in database 'master', an error occurred at log record ID (2041:52:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Error: 3313, Severity: 21, State: 1.
Could not redo log record (2041:52:2), for transaction ID (0:0), on page (1:19), allocation unit 458752, database 'master' (database ID 1). Page: LSN = (2040:369:229), allocation unit = 458752, type = 1. Log: OpCode = 18, context 2, PrevPageLSN: (2040:349:127). Restore from a backup of the database, or repair the database.
Error: 3456, Severity: 21, State: 1.
Starting up database 'master'.

Never a good day when this happens, especially on a clustered sql server, there goes your HA and your 5 9's !

What now ?

Figure out what happened, why is my master database corrupt (in this case the cluster crashed because of an "unintended consequence" of a network change).  If it's something like hardware or some other physical problem, get that corrected.

Once that's corrected you still have to recover the master database.

If your like me, you have good backups, but they are all .BAK files.  How can your restore a .BAK file, if you can't even get SQL to start ?

There are ways and any of them work, here's what you do.

1.  Just in case things don't work, and time is critical, have someone else start a support call with Microsoft, just in case.
2.  Copy your existing master.mdf and ldf to some safe location (I usually make a sub-directory under where-ever the system databases are), this is "just in case".
3.  Get a copy of master.mdf and ldf from some where of the same version and preferably service-pack and CU.
       a.  You can get a copy from the installation media (mdf and ldf)
       b.  I think there is a rebuildm.exe utility on installation media.
           1.  I don't have time to mess around with installation media so I usually go with something else
       c.  You can grab another Master mdf and l
       d.  I restore the master_{some timestamp}.bak to another sql server
           1.  copy the .bak file to another sql server (of same version)
           2.  restore the .bak, but with a different database name, master_tmp
               - keep the data files named the same master.mdf and mastlog.ldf
               - you may need to ensure a different path than the existing master.mdf and ldf to avoid failure on restore
           3.  detach the newly restored database master_tmp
           4.  now you have master.mdf and mastlog.ldf
4.  Copy the master.mdf and ldf from step above, over-writing the existing master.mdf and ldf
5.  ensure in cluster manager to put the sql server services "offline" (they should be in a "failed" state now).
6.  Start SQL Server manually from a command prompt (enusre to start command prompt with administrative privileges)
7.  {find the correct path to }\sqlserver.exe -m -s {instance name}
8.  once started you really still need to restore the database from the .bak, as the overlay method of restoring master with a different name and then copying the files doesn't quite leave the state the same as a restore
9.  restore {master} from disk='' with replace
10.  SQL Server should automatically stop
11.  you should now be able to start sql from cluster manager and all should be good.

Blog Home