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.
Check out Kimberly and
Paul’s blog, Kimberly & Paul:
SQL Server Questions
Is the Recovery Interval
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.
Crash recovery has two tasks to perform: replaying log records from
committed transactions (called REDO) and removing the effect of log
SQL Server Pro / May 2013