Part 4

Backups and Restores

Objectives
 Backup Fundamentals
 Recovery Models
 Backup Types
 Backup Media
 Backup Strategies
 Restoring a Database
 Discuss Backup Strategies
Backup Fundamentals

Three types of backup within SQL Server: full,

differential, and log.

Configuring a database in one of three recovery

models: SIMPLE, FULL, and BULK LOGGED.
SIMPLE Recovery Model
• Transaction log (or VLF) contain transactions is
truncated after each checkpoint operation.
• Not have to administer the transaction log, it also means
that can’t take transaction log backups.

Advantage - Increasing performance, for some operations,

because transactions are minimally logged :
Bulk imports ; SELECT INTO ;
UPDATE statements against large data types that use
the WRITE clause ;
WRITETEXT ; UPDATETEXT ;
Index creation ; Index rebuilds.
SIMPLE Recovery Model
Disadvantage
• not possible to recover to a specific point in time;
only restore to the end of a full backup.
• incompatible with some SQL Server HA/DR
features :
o AlwaysOn Availability Groups
o Database mirroring
o Log shipping
SIMPLE Recovery Model

The most appropriate way to use the SIMPLE

recovery model is for large data warehouse–style
applications (nightly ETL load, read-only for the
rest of the day.
FULL Recovery Model
• Log truncation does not occur after a
CHECKPOINT operation. Instead, it occurs
after a transaction log backup.
• Must schedule transaction log backups to run on
a frequent basis.
• Many factors can cause the VLFs within a
transaction log not to be truncated.This is known
as delayed truncation.
• Failing transaction log backups
o unrecoverable in the event of a failure
o transaction log continues to grow until it runs out of
space.
FULL Recovery Model
Advantage
o point-in-time recovery.
o compatible with all SQL Server functionality

Best choice of recovery model for production

databases.
Tip switch from SIMPLE recovery model to
FULL recovery model, not actually in FULL
recovery model until take a transaction log backup.
BULK LOGGED Recovery Model
• Be used on a short-term basis while a bulk import
operation takes place.
• Cannot use any transaction log backups that
contain minimally logged transactions for point-
in-time recovery.
=> Take a transaction log backup
o before switching to the BULK LOGGED recovery model.
o after switching back to FULL recovery model.
• Disabling any logins, except for the login that
performs the bulk import to ensure that no other
data modifications take place.
BULK LOGGED Recovery Model
During the bulk import, transaction log backup is
more expensive than it is in FULL recovery model
in terms of IO, because SQL Server also backs up
any data extents, which contain pages that have
been altered using minimally logged transactions.

BULK LOGGED recovery model forces data pages

updated with minimally logged pages to flush to
disk as soon as the operation completes instead of
waiting for a checkpoint operation.
Backup Types
Three types of backup in SQL Server: full,
differential, and log.
Full Backup : take a full backup in any recovery
model.
When issue a backup command :
o First issues a CHECKPOINT.
o Second backs up every page within the database
(data read phase)
o Finally backs up enough of the transaction log (log
read phase)
Backup Types
Differential Backup : backs up every page in the
database that has been modified since the last full
backup.

• SQL Server keeps track of these pages by using

bitmap pages called DIFF pages.
• Cumulative nature of differential backups means
that restore chain only ever needs to include one
differential backup—the latest one.
Backup Types
Log Backup : only be taken in the FULL or BULK
LOGGED recovery models.
• In the FULL recovery model, it backs up all transaction
log records since the last backup.
• In the BULK LOGGED recovery model, it also backs
up any pages that include minimally logged
transactions.
• When the backup is complete, truncates VLFs within
the transaction log until the first active VLF is reached.
• Transaction log backups are especially important on
databases that support OLTP.
Backup Media
Backup media consists of backup devices, logical backup
devices, media sets, media families, and backup sets.
Backup device is a physical file on disk, a tape, or a
Windows Azure Blob.
Media set can contain a maximum of 64 backup devices,
data can be striped across the backup devices and can also
be mirrored.
Striping backup can be useful for a large database, allowing
to place each device on a different drive array to increase
throughput.
Using a mirror, the contents of each device are duplicated
to an additional device for redundancy.
Backup Media
If one backup device in a media set is mirrored,
then all devices within the media set must be
mirrored. Each device can have up to four mirrors.
Logical backup device is an instance-level object,
creating in SSMS by choosing New Backup Device
from the context menu of Server Objects ➤ Backup
Devices
or using T-sql :
Backup Media
Each media family within a media set is assigned a
sequential number, called family sequence number.
Each physical device is allocated a physical
sequence number.
When a media set is created, backup devices (files
or tapes) are formatted, and a media header is
written to each device.
Media header contains name of media set,GUID of
media set, GUIDs and sequence numbers of media
families,number of mirrors in the set, and date/time
header was written.
Backup Media
Backup Media
Each time a backup is taken to media set, called
backup set.
New backup sets can be appended to the media, or
overwrite the existing backup sets.
If the media set contains only one media family,
then that media family contains the entire backup
set. Otherwise, backup set is distributed across the
media families.
Each backup set within the media set is given a
sequential number; allowing to select backup set to
restore.
Backup Strategies
DBA can implement numerous backup strategies
for a database,based on the RTO (recovery time
objective) and RPO (recovery point objective).
Backup Strategies
Full Backup Only - only take full backups are the
least flexible.
Full Backup appropriate strategies
o If databases are infrequently updated and there is a
regular backup window that is long enough to take a full
backup.
o A full backup–only strategy is often used for the Master
and MSDB system databases.
o It may also be appropriate for user databases, which are
used for reporting only and are not updated by users. In
this scenario, it may be that the only updates to the
database are made via an ETL load.
Backup Strategies
Full and Transaction Log Backups - If database is in
FULL recovery model, can to take transaction log
backups, as well as full backups.
Appropriate for databases that are updated
throughout the day, can to restore to a point in time
just before a disaster occurred.

Scheduling log backups to be in line with your RPO

For example : RPO of 30 minutes, can schedule log
backups to occur every half hour.
Backup Strategies
When using this strategy, should also consider RTO.
o RPO of 30 minutes, taking transaction log
backups every half hour.
o only taking a full backup once per week, at 01:00
on a Saturday.
database becomes corrupt on Friday night at 23:00,
need to restore 330 backups.
=>may not be able to restore the database within
the allotted time.
Backup Strategies
Full, Differential, and Transaction Log Backups -
differential backup is cumulative, as opposed to
incremental in log backups.
o RPO of 30 minutes, taking transaction log
backups every half hour.
o only taking a full backup once per week, at 01:00
on a Saturday.
o differential backup on a nightly basis at 01:00
database becomes corrupt on Friday night at 23:00,
only need to restore 43 backups.
Backup Strategies
Filegroup Backups - For very large databases, may
be able to split data across filegroups and back up
half of filegroups on alternate nights.

Restore scenario : to restore only the filegroup that

contains the corrupt data.
Backup Strategies
Partial Backup - involves backing up all read/write
filegroups, but not backing up any read-only
filegroups.
Restoring a Database
Restore a database either through SSMS or via T-
SQL.

Restoring Files and Pages - The ability to restore a

filegroup, a file, or even a page gives you great
control and flexibility in disaster recovery
scenarios.
Discuss Backup Strategies