SQL Server is architected so that you can recover ALL of the committed transactions right up to the point of failure. This fact did not go unnoticed by financial services companies on Wall Street during the early days of distributed applications - the early days of SQL Server. To these companies, even a few minutes of lost transactions can mean millions of dollars - yes, time is money. But we still need to practice disaster recovery, so that when disaster does strike, we are ready to play the hero. As a colleague of mine always says, you want to avoid any "resume generating events!"

The point of failure recovery assumes that you can back up the "tail" of the transaction log AFTER the failure. The tail of the log contains "gold dust" - all those latest transactions that have committed since the last backup. That is why we store the log file on RAID-1 mirrored volumes so that even in the event of disk failure, we can get hold of a copy so we can back it up. In this way, the log file is the most important file in the database; the data files can always be recovered from a full backup. But those last minute changes before a failure are only contained in the active transaction log. So whatever you do, make sure you backup the active transaction log before a restore operation. In fact, when teaching I always tell students to remember to do two things before attempting a classic database restore:

1. Restrict access to the database (stop people updating the database!)
2. Backup the "tail" of the transaction log (capture those last minute changes!)

And with SQL Server 2005 Enterprise Edition, you can now perform Online Restores - you may not have to restrict access at all. This means that you only need to remember ONE thing before a restore operation: Backup the "tail" of the transaction log!
If you remember this, then you can rest assured that you will be the hero.
Cheers
Brian

Tail-Log Backups on MSDN: http://msdn.microsoft.com/en-us/library/ms179314.aspx

Recent blog posts...

Log File too big? Try some significant shrinkage!

So I can’t use BACKUP LOG WITH TRUNCATE ONLY – but what can I do instead?