Differential Partial Backups

This topic is relevant for all SQL Server databases.

Differential partial backups are used only with partial backups. A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential. If only some of the data that is captured by a partial backup has changed, a differential partial backup is smaller than the base, and is faster to create. For a large database, taking differential backup facilitates making frequent backups of the data to decrease the risk of data loss.

However, restoring from differential partial backups will necessarily take more steps and time than restoring from a partial backup. Also, the restore process is more complex because two backup files are involved.

A differential partial backup is intended for use with a single differential base. Trying to create a multibase differential partial backup causes an error. For more information, see Base of a Differential Backup.

Note

For more information about how to use differential backups, including best practices, see Using Differential Backups.

Effect of Adding, Deleting, or Modifying Filegroups on Partial Differential Backups

The following table defines whether a filegroup is automatically included in a differential partial backup. This is based on whether the filegroup has been added, deleted, or its access has changed since the partial backup that is the base.

Change to filegroup (since partial backup)

Is this filegroup included or excluded?

Filegroup deleted

Excluded

Note:

Restoring the differential backup drops the filegroup.

Read-only filegroup added

Excluded

Note:

Before you create a partial differential backup, you should create a separate, full file backup of the added filegroup. If the filegroup has not been backed up, the SQL Server Database Engine issues a warning, and the differential partial backup succeeds without backing up the read-only filegroup.

Read/write filegroup added

Included

Note:

Restoring the differential backup will restore the new filegroup.

Filegroup changed to read/write

Included if either of the following is true for the filegroup; otherwise, the backup fails:

• Was included in the base partial backup when the filegroup was still read-only
• Has never been backed up.

Filegroup changed to read-only

Included

Note

For information about how to work with partial and differential partial backups after changing a filegroup from read/write permission to read-only access or from read-only to read/write permission, see "Creating Partial Backups After Changing Access to a Filegroup," later in this topic.

Differential Partial Backup Strategy (Simple Recovery Model)

The following illustration shows a backup strategy that combines full and differential partial backups with a full file backup of the read-only filegroups. The first backup on the database is a full partial backup. This includes the primary filegroup and the read-only secondary filegroup, A. The next backup is a full file backup of the two read-only filegroups, B and C. Next, two differential partial backups are taken. Finally, a second full partial backup is created to provide a new base for the next differential partial backup (not shown).

Creating Differential Partial Backups

To create a differential partial backup, you must use the BACKUP statement. The statement must include the DIFFERENTIAL option and also the READ_WRITE_FILEGROUPS option. If the most recent partial backup (the differential base) includes any read-only file or filegroups, you must also specify each of them in the statement. The required BACKUP syntax for creating a differential partial backup is:

BACKUP DATABASE database_name READ_WRITE_FILEGROUPS [ ,<file_filegroup_list> ] TO <backup_device> WITH DIFFERENTIAL

Note

Partial backups are not supported by SQL Server Management Studio or the Maintenance Plan Wizard.

Creating Partial Backups After Changing Access to a Filegroup

This section is relevant only for databases that contain multiple filegroups; and, under the simple model, only for read-only filegroups.

Differential partial backups work with partial backups as expected in most situations. However, a problem can occur if read-only or read/write permission to a filegroup changes in the time between taking a partial backup and taking a differential partial backup. When you try a differential partial backup, you might encounter an error, depending on whether the file is included in the differential partial backup. For information about whether a filegroup is automatically included in the differential partial backup, see Differential Partial Backups.

Best practice:   After changing a filegroup to read/write, on the next backup, you should take a partial backup instead of a differential partial. The only exception is when you intend to change the filegroup back to read-only before the next backup.

Best practice: After changing a filegroup to read-only access, take a full file backup of the filegroup, so that subsequent differential partial backups to automatically skip the filegroup.

See Also

Concepts

Backup Under the Full Recovery Model
Backup Under the Simple Recovery Model
Differential File Backups
Full File Backups
Partial Backups
Read-Only Filegroups and Compression

Other Resources

Using Differential Backups

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release

History

17 July 2006

New content: Added summary of the basic BACKUP syntax for creating a differential partial backup. Changed content: Revised the table summarizing the effect of adding, deleting, or modifying filegroups on partial differential backups. Incorporated the former "Creating Partial Backups After Changing Access to a Filegroup" topic. Revised the best practice about taking a partial backup after changing a filegroup from read-only to read/write.