SQLTeam.com | Weblogs | Forums

SQL 2008 R2 - MASTER.LDF corrupted without backup - Can I start SQL without MASTER LOG?


#1

Hello,

I have a SQL server that has a corrupt MASTER.LDF file, which prevents SQL services from starting at all.

Is there any way to start SQL services with just MASTER.MDF? So far I have tried renaming MASTER.LDF to MASTER.LDF.OLD and SQL reports that the file is missing and still does not start (although I do not get a corrupt message).

If I cannot start MASTER.MDF without the log file, is there a way to rebuild the log file?

Help is appreciated. Thanks!


#2

If your LDF file is lost, you will likely lose some data. If you have a full backup and full set of log backups (unbroken log chain), the first thing I would try is to take a tail of the log backup and then try to restore the database (to a dev server or using a new name) using the full db, log backps, and the tail of the log backup. That may fail at some point if the log backups and/or the full backup are corrupt because of the LDF corruption. You can recover to the log backup that was successfully restored.

Another option would be to put the database in emergency mode (so you can access it and run DBCC with REPAIR_ALLOW_DATA_LOSS. This may or may not work. Refer to Paul Randal's article for how to do this.

This article also might be helpful.
See an article about how to take tail of the log backup and restore here


#3

Ooh, that's a bad situation. The master db is so critical to the way SQL works you can only restore it, not try to recover it and/or rebuild its log.

About the only chance you might have -- and it definitely may not work -- is to copy the master.mdf to another location. Rebuild the master db. Stop SQL Server. Copy your original master.mdf over the rebuilt master.mdf. Then try to start SQL. If somehow SQL is then able to start, immediately backup the master db!