Database Reference
In-Depth Information
Summary
A full database backup stores a copy of the database that represents its state at the time when the backup finishes.
Differential backup stores extents that have been modified since the last full backup. Log backups store the portion of
the transaction log starting from the last full or the end of the last log backup.
Full and differential backups are supported in every recovery model; while log backup is supported only in the
FULL or BULK-LOGGED recovery models.
Differential backups are cumulative. Every backup contains all of the extents modified since the last full backup.
You can restore the latest differential backup when needed. Conversely, log backups are incremental and do not
contain the part of the transaction log backed up by the previous backups.
A full backup and sequence of log backups makes up a log chain. You should restore all of the backups from
a chain in the right order when restoring a database. You can use the
COPY_ONLY
option with full or log backups to
keep the log chain intact.
The frequency of log backups is dictated by Recovery Point Objective (RPO) requirements. The log should be
backed up in intervals that do not exceed the allowable data loss for a system.
A Recovery Time Objective (RTO) specifies the maximum duration of the recovery process, which affects full
and differential backup schedules. You should also factor in the time required to transmit files over the network when
designing a backup strategy. Backup compression can help reduce this time and improve the performance of backup
and restore operations, but at a cost of extra CPU load and extra time as compression and decompression of the data
takes place.
You should validate backup files and make sure that your backup strategy is valid and meets the RTO and RPO
requirements. The duration of the backup and restore processes changes over time along with database size and load.
SQL Server Enterprise Edition supports piecemeal restore, which allows you to restore data on a file and filegroup
basis, keeping part of the database online. This feature greatly improves the availability of the system and helps to
reduce the recovery time of critical operational data when the data is properly partitioned.
You can exclude read-only data from regular full backups, which can reduce backup time and the size of backup
files. Consider putting read-only data into a separate filegroup and marking it as read-only when appropriate.
