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

Question

Assuming data files are on D: 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?

Answer 1

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 here.

Answer 2

You can try with this undocumented command after having unattached the mdf file.

USE master;

CREATE DATABASE [Mydb]
    ON  ( FILENAME = N'E:\xxx\Mydb.mdf' )
    FOR ATTACH_FORCE_REBUILD_LOG;
GO

This will rewbuild a new ldf file. What remain inside the mdf file is left to 'the best effort'.

Answer 3

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).

It sounds like your current Backup frequency does not support your intended Recovery Objectives.
Remember the two most important Metrics about Recovery:

• Recovery Time Objective (RTO)
  How long do you have to get the broken database back up and running again?
• Recovery Point Objective (RPO)
  How much Data are you allowed to lose in doing so?

If, for example, your RPO is four hours, then you need to be taking Log backups every two hours.

How to recover the database in this scenario?

From your data and log backups.

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?

Data loss will be only those transactions that were not committed. As long as they're in the (written-ahead) Transaction log and committed, the Instance Recovery will be able to reconstitute them, even if they were never written out to disk.