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 .gif "Note") 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:
|
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/write 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).
.gif "Combining partial and filegroup backups")
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.