Microsoft SQL Server 2012 Bible

Recovery Models

The recovery model configures SQL Server database settings to accomplish the type of recovery required for the database, as shown in Table 21.1. The key differences among the recovery models involve how the transaction log behaves and which data is logged.

Table 21.1 SQL Server Recovery Models

Although the durability of the transaction is configurable, the transaction log is still used as a write-ahead transaction log to ensure that each transaction is atomic. In case of system failure, SQL uses the transaction log to roll back any uncommitted transactions and to complete any committed transactions.

Simple Recovery Model

The simple recovery model is suitable for databases that require that each transaction be atomic but not necessarily durable. The simple recovery model directs SQL Server to truncate, or empty, the transaction log on checkpoints. The transaction log keeps a transaction until it is confirmed in the data file, but after that point the space may be reused by another transaction in a round-robin style. This is the reason why a simple recovery model does not support a transaction log backup.

A simple recovery model has the benefit of keeping the transaction log small, at the cost of potentially losing all transactions since the last full or differential ...