How to recover from a SQL server log file drive crash?



  • Assuming data files are on 😧 and Log files are on E:

    Say the E drive crashes and the log files (.ldf) are lost. I attach a new empty disk to the E: and start the sql server.

    Upon startup, SQL Server will realize that the .mdf files are present, but the log files are absent. Due to missing log files, the SQL server will be unable to do any of its undo/redo recovery steps and it will place the database as a suspect.

    Note: I tried this and notice that database goes into suspect mode. The best practice here is to use the backups (full + tlog) to restore the database. In my situation I have the full backup from 12am but no tlog backups. If I restore from the 12am backup then I loose entire days data (assuming crash happened mid-day). I am trying to think how to proceed in such situation by utilizing the existing data file.

    1. How to recover the database in this scenario?
    2. Will it be correct to say that data loss will be only those transactions that were in the tlog file but not written to the disk?


  • How to recover the database in this scenario?

    Restore from your backups. Read on and you'll understand why we recommend the restore route.

    Will it be correct to say that data loss will be only those transactions that were in the tlog file but not written to the disk?

    I wouldn't put it that way. Your database is, as you noted, suspect. So you can't access it. Whatever means you do to "force" this database to be accessible will give you an inconsistent database. To mention a couple of examples:

    1. Physical inconsistencies: for example pages written for index modifications while the corresponding data pages were not written.

    2. Logical inconsistencies: for example orders without order detail rows. Or the other way around.

    All bets are off. So if you force access to a database which wasn't cleanly shutdown, without the log file, you need to tidy up above.

    Physical inconsistency can possibly be dealt using DBCC CHECKDB and REPAIR_ALLOW_DATA_LOSS. It will of course just throw away whatever pages that doesn't look right. Are you prepared to do that? Also, this can add more logical inconsistencies (to what you already have from step 2 above).

    Logical inconsistencies are for you to handle. Is the database small enough to go through all data to make sure it is logically consistent? Probably not. You understand now why restore is the way to go, and why frequent log backups are so important.

    As for how to force access to a database in this state, you have two options:

    1. Attach it using ATTACH_FORCE_REBUILD_LOG. This isn't documented so I wouldn't go this route.

    2. Use ALTER DATABASE to set it to EMERGENCY mode. And then CHECKDB with REPAIR_ALLOW_DATA_LOSS. This process is described https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/ .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2