Recovering a database with a missing transaction log

Question: We had a SAN problem over the weekend and the upshot is that one of our main databases was shut down with open transactions, but we’ve lost the log file. The last working backup is two weeks old.

Is there any way to recover the database without having to resort to the old backup?

Answer: Yes, but not without consequences.

Usually when a database has open transactions and the server crashes, crash recovery will run on the affected database and roll back the open transactions. This prevents the partial effects of transactions being present in the database. If the transaction log is not available when SQL Server starts, the database will be in the SUSPECT state.

In this case, the only way to bring the database online (note that I'm not saying ‘way to make the database usable’) is to use the emergency mode repair functionality that was added in SQL Server 2005. This basically builds a new transaction log and then runs a DBCC CHECKDB using REPAIR_ALLOW_DATA_LOSS. Read more.

The problem you will have if you decide to go this route is that emergency mode repair cannot roll back any of the active transactions as it has no knowledge of what they were, because the transaction log was destroyed. This means that at best, the resulting database will be transactionally inconsistent – i.e. the state of the data in the database is unknown.

For instance, there may have been a transaction that was updating some sales records in a table, and only half of them were updated before the crash and subsequent transaction log loss occurred. You’ll find it very difficult to figure out what state the data is in, and how to make the database properly usable again by the application.

Emergency mode repair is supposed to be a real last resort when all other methods of recovering data have failed.

In your case, you’ll have to figure out what is the lesser of two evils – recovering the database into an inconsistent state, or restoring the two-week old backup. You may end up deciding to do both, and trying to piece together the data, but that will be very time consuming and problematic – again, because you don’t know what was happening in the database at the time that the crash occurred.

To prevent this situation in future, beef up your backup strategy to use much more frequent backups, and add a high-availability technology that maintains a real-time copy of your database—such as database mirroring or SQL Server 2012 Availability Groups.

[Unrelated: this is the last post on this blog, as we’re going to focus on our own blogs moving forward. It’s been great having this additional outlet to the community and we’ve enjoyed writing the weekly posts for the last two years. We hope you’ve enjoyed reading and learning from them too!]