SQL Server Pro - May 2013

when the server crashed and the transaction log was lost. You’ll find it very difficult to figure out what state the data is in and how to make the database properly usable again.

Emergency mode repair is supposed to be the 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 might end up deciding to do both and trying to piece together the data, but that will be very time consuming and problematic because you don’t know what was happening in the database at the time the crash occurred.

To prevent this situation in the future, you should beef up your backup strategy so that backups are performed much more frequently. You should also add a high-availability technology that maintains a real-time copy of your database, such as database mirroring or SQL Server 2012 Availability Groups.

Editor’s
Note
Check out Kimberly and
Paul’s blog, Kimberly & Paul:
SQL Server Questions
Answered.

Blog

Is the Recovery Interval
Guaranteed?

SQL Server has a one-minute recovery interval for all databases, and I’ve read that SQL Server guarantees that the databases can always be brought online after a crash within one minute. Can you confirm this? It seems unlikely to me.

The quick answer is that the recovery interval isn’t guaranteed in any way—it’s simply a goal. People who state that it’s an absolute value that SQL Server can always achieve have a fundamental misunderstanding of how logging and recovery works.