Database Reference
In-Depth Information
Chapter 30
Designing a Backup Strategy
In the technology world, it is just matter of time before disaster happens. A database could become corrupted due to
a user error, hardware malfunction, or software bug. A disk array could fail, making databases unavailable to users.
An engineer can accidently change the LUN configuration in a SAN array and affect the database it stores. A natural
disaster could affect the availability of a datacenter. In any of these instances, it is essential to recover the database and
bring the system online with minimal data loss and downtime. Thus an appropriate backup strategy is a key element
of every SQL Server installation.
In this chapter, we will discuss how to design backup strategies in a way that minimizes system downtime and
data loss. It is impossible to touch that subject without talking about database backup, restore, and recovery processes
in detail, which we will also cover in this chapter.
Database Backup Types
There are three different types of database backups available in SQL Server.
A
Full
database backup backs up the whole database. SQL Server performs a CHECKPOINT as the first step of
database backup, backs up all allocated extents from the data files and, finally, backs up the portion of transaction
log required to recover the database after a restore. That portion includes all log records starting from the oldest of
these events:
The last CHECKPOINT.
The beginning of the oldest active transaction.
The beginning of the unscanned portion of the log if there are any processes that rely on
the transaction log scan, such as Transactional Replication, Database Mirroring, AlwaysOn
Availability Groups, and others.
A
full
database backup represents the database at the time when the backup operation is finished. It is supported
in every recovery model.
A di
fferential
backup backs up extents, which have been modified since the last full backup. SQL Server tracks
what extents have been changed with a special type of allocation map pages called
Differential Changed Map (DCM)
.
SQL Server clears those map pages only during a full database backup. Therefore, differential backups are cumulative,
and each of them stores all extents that have been modified since the last full, rather than last differential, backup.
Like a full database backup, differential backups work in every recovery model.
A
Log
backup backs up the active portion of transaction log starting with the LSN of last full or log backup.
This backup type is only supported in the FULL or BULK-LOGGED recovery models, and it is an essential part of
transaction log management, which is required to trigger log truncation.
