SQL Server offers simple, full, and bulk-logged recovery models. For the most part, you should choose full, which allows you to back up both the database and the transaction log. You can back up a transaction log quickly and frequently; every few minutes isn't too often if data is critical.
If the worst happens, back up the current transaction log first. Then, restore the last full database backup, and all subsequent transaction log backups. For instance, suppose you adhere to the following backup schedule and a failure occurs at 9:00 PM:
|8:00 AM||Back up database|
|10:00 AM||Back up transaction log|
|12:00 PM||Back up database|
|2:00 PM||Back up transaction log|
|4:00 PM||Back up transaction log|
|6:00 PM||Back up database|
|8:00 PM||Back up transaction log|
First, you'd back up the 8:00 PM transaction log. Then, you'd restore the database using the last database backup from 6:00 PM. Finally, you'd apply the 8:00 PM transaction log backup and the active transaction log. (Differential backups are a bit more complex.)
After backing up a transaction log, SQL Server truncates the log's inactive section and reuses it to store new transactions. That way, the log doesn't grow uncontrollably large. Remember, SQL Server doesn't use the inactive items during recovery because those transactions are already complete.
If possible, don't store a database and its backup and transaction logs on the same server. Store these files on different physical disks, ideally located in different buildings.
Some experts suggest using the simple recovery model because SQL Server truncates the transaction log at every checkpoint, which keeps the transaction log at a manageable size. If you follow this advice, however, you'll be living on the wild side. In a crash, you'll lose everything up to the last backup because the simple recovery model offers no transaction log with which to restore from the last backup to the crash. So be sure to back up the database frequently if you opt for simple recovery.
On the other hand, if you seldom change data, or change only a few items frequently, simple can be more efficient, but that's really the only good reason to consider it. Otherwise, the full model is the way to go.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex.
This article was first published on Devx.com.