Microsoft® SQL Server® 2012 Bible

549

Chapter 21: Backup and Recovery Planning

21

■ (^) Differential: Backup of all data pages modifi ed since the last full backup.
■ Partial: Backup of primary fi legroup, every read/write fi legroup, and any optionally
specifi ed read-only fi les.
■ Transaction log: Backup of all transactions in the log.
■ (^) File or filegroup: Backup of all the data in the fi le or fi legroup.
■ File differential: Backup of all data pages modifi ed since the last fi le or fi legroup
backup.
■ Copy-only: Backup all the data without affecting the overall backup and restore
procedures for the database. In other words this allows you to take a full backup of
the database without breaking the existing backup chain.
Backing up the database may not be the only critical backup you must perform. If the database-security
scheme relies on SQL Server authentication, backing up the database users is important as well. In SQL Server
2012 another important feature, called Contained Databases, makes this user account management much
easier.
SQL Server backups are fl exible and can handle any backup-to-fi le ratio. A single backup
instance can be spread across several backup fi les, creating a backup set. Conversely, a
single backup set can contain multiple backup instances.
Restoration always begins with a full backup. Differential and transaction log backups then
restore the transaction that occurred after the full backup.

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

Recovery Model Description

Transaction

Atomicity

Transaction

Durability

Bulk-Copy Operations

(Select Into and Bulk Insert)

Simple Transaction log is

continuously

truncated on

checkpoints.

Yes No, can restore

only to the last

full or differen-

tial backup.

Minimally

logged — high

performance

Continues

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

http://www.it-ebooks.info