Microsoft® SQL Server® 2012 Bible

550

Part V: Enterprise Data Management

Recovery Model Description

Transaction

Atomicity

Transaction

Durability

Bulk-Copy Operations

(Select Into and Bulk Insert)

Bulk-Logged Bulked

operations are

minimally logged,

and all other

transactions are

fully logged.

Minimal logging

logs only the

information

required to

recover the

transaction but

does not allow

point-in-time

recovery.

Yes Maybe, can

restore only to

the last full or

differential

backup, or to

the last trans-

action-log

backup if no

bulk-copy

operations

have been

performed.

Minimally

logged — high

performance

Full All transactions

are logged and

stored until trans-

action-log

backup.

Yes Yes, can

restore up to

the point of

recovery.

Slower than simple or

bulk-logged

Although the durability of the transaction is confi gurable, 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 confi rmed in the data fi le, 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 benefi t of keeping the transaction log small, at the cost of

potentially losing all transactions since the last full or differential backup.

A recovery plan based on a simple recovery model might include performing full backups

once a week and differential backups every weeknight, as shown in Figure 21-1. The full

backup copies the entire database, and the differential backup copies all changes that have

been made since the last full backup.

TABLE 21-1 (continued)

c21.indd 550c21.indd 550 7/31/2012 9:22:46 AM7/31/2012 9:22:46 AM

http://www.it-ebooks.info