O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

c21tnt001 c21tnt001

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required