The scope of a backup of data (a data backup) can be a whole database, a partial database, or a set of files or filegroups. For each of these, SQL Server supports full and differential backups:
-
Full backup
A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
-
Differential backup
A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential base is a full backup of read/write data. A differential backup contains only the data that has changed since the differential base. Typically, differential backups that are taken fairly soon after the base backup are smaller and faster to create than the base of a full backup. Therefore, using differential backups can speed up the process of making frequent backups to decrease the risk of data loss. Usually, a differential base is used by several successive differential backups. At restore time, the full backup is restored first, followed by the most recent differential backup.
Over time, as a database is updated, the amount of data that is included in differential backups increases. This makes the backup slower to create and to restore. Eventually, another full backup will have to be created to provide a new differential base for another series of differential backups.
.gif) Note: |
|---|
|
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. For more information, see Working with Multibase Differential Backups.
|
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 database backups
|
A backup of all files in the database. This backup contains only the data extents that were modified since the most recent database backup of each file.
|
Partial Backups
Partial and differential partial backups are new 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 2005 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 partial backup
|
A backup that contains only the data extents that were modified since the most 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.
SQL Server supports the following types of file backups.
|
Backup type
|
Description
|
|---|
|
File backup
|
A full backup of all the data in one or more files, or filegroups.
| Important: |
Under the simple recovery model, file backups are basically restricted to read-only secondary filegroups. You can create a file backup of a read/write filegroup, but before you can restore the read/write file backup, you must set the filegroup to read-only and take a differential read-only file backup.
|
|
Differential file backups
|
A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.
| Note: |
Under the simple recovery model, this assumes that the data has been changed to read-only since the full backup.
|