Page 139 - DCAP508_DATABASE_ADMINISTRATION

Unit 10: Database Backup and Restore

Notes

Notes Typically, a differential backup covers the same data files as those files that are
covered in a single differential base. Under the simple recovery model, a differential
backup can have only one differential base. Trying to use multiple bases causes an error
and the backup operation fails. Under the full recovery model, differential file backups
can use multiple bases, but this can be difficult to manage.
Each data backup includes part of the transaction log so that the backup can be recovered to the
end of that backup.

After the first data backup, under the full recovery model or bulk-logged recovery model,
regular transaction log backups (or log backups) are required. Each log backup covers the part
of the transaction log that was active when the backup was created, and the log backup includes
all log records that were not backed up in a previous log backup.

Database Backups

Database backups are easy to use and are recommended whenever database size allows. SQL
Server supports the following types of database backups.

Backup type Description
Database backup A full backup of the whole database. Database backups represent the whole
database at the time the backup finished.
Differential A backup of all files in the database. This backup contains only the data extents
database backups that were modified since the most recent database backup of each file.

Partial Backups

Partial and differential partial backups were introduced in SQL Server 2005. These backups are
designed to provide more flexibility for backing up databases that contain some read-only
filegroups under the simple recovery model. However, these backups are supported by all
recovery models.
SQL Server 2008 supports the following types of file backups.

Backup type Description
Partial backup A backup of all the full data in the primary filegroup, every read/write filegroup,
and any optionally specified read-only files or filegroups. A partial backup of a read-
only database contains only the primary filegroup.
Differential A backup that contains only the data extents that were modified since the most
partial backup recent partial backup of the same set of filegroups.

File Backups

The files in a database can be backed up and restored individually. Using file backups can
increase the speed of recovery by letting you restore only damaged files, without restoring the
rest of the database. For example, if a database consists of several files that are located on
different disks and one disk fails, only the file on the failed disk has to be restored. However,
planning and restoring file backups can be complex; therefore, file backups should be used only
where they clearly add value to your restore plan.

LOVELY PROFESSIONAL UNIVERSITY 133