In the previous section of this article we explored the data protection provided by full and differential backups. In most circumstances, the cost of performing these large-scale backups prohibits us from doing them during high-use periods, such as business hours. However, the failure to perform backups during business hours could result in the loss of an entire day's data if the database fails before the backup is performed. Fortunately, SQL Server provides us with the capability to backup transaction logs -- a feature that provides an efficient mechanism to decrease the amount of potential data loss.

During the course of normal operations, SQL Server utilizes a transaction log to track all of the modifications performed within a database. This log ensures both that the database is able to recover when abruptly interrupted (such as a loss of power) and that users are able to undo (or "rollback" in database lingo) the results of a database transaction. Unfinished transactions are maintained in the log before they are permanently stored (or "committed") in the database. The transaction log backup functionality enables us to store copies of the database's transaction log on a backup device. These backups generally consume far fewer resources than a full or differential database backup and therefore are suitable for even high-use periods.

Each time the transaction log is backed up, SQL Server removes all of the committed transactions in the log and writes them to the backup media. Due to this incremental process, transaction logs are not cumulative and we must maintain a complete set of transaction logs reaching back to the most recent full or differential backup. For example, let's return to the organization described in the previous section of this article. Recall that the XYZ Company performed full backups at the close of business every Friday and differential backups every Monday through Thursday evening. Let's take this strategy a step further and include hourly backups of the transaction log during business hours. Assume a database failure at 11:05AM Wednesday. Under the previous strategy, we used Friday's full backup and Tuesday's differential backup to restore the database to it's state at the close of business Tuesday. This resulted in a loss of two hours of data (9-11AM Wednesday). Using the new strategy that incorporates transaction log backups, we can apply the 9AM and 10AM transaction log backups after the Tuesday differential backup to restore our database to it's state at 11AM Wednesday. Now we've managed to restore all but five minutes of database activity, clearly a vast improvement.

The previous example highlights the importance of a comprehensive backup strategy. XYZ Corporation's judicious use of database and transaction log backups results in a disaster recovery model where no more than an hour's data should be lost after a catastrophic event with a minimal impact on system performance. You can take these examples and modify them to meet the requirements you defined for your organization after reading the first section of this article.

Now that you've been introduced to the basic concepts behind SQL Server disaster recovery, it's time to begin developing a disaster recovery plan for your database. Remember to involve both your internal customers and management team in this process. If you're stumped and need advice or just want to run your plan by your peers, stop by our forum and join in the ongoing discussion.