Partial Differential Backup

A differential partial backup is a type of partial backup. The partial backup feature was added in SQL Server 2005, and is designed for databases where the tables are broken up into filegroups, and some of the filegroups are read-only (typically found in data warehouse environments).

A partial differential backup is differential relative to the base backup. A partial differential backup records only records filegroup data that has been modified since the previous partial backup. The previous partial back up is called the base for the differential. A partial differential backup is smaller than the base, making it faster and easier to create. According to the Microsoft SQL Server TechNet, performing a differential partial backup “facilitates making frequent backups of the data to decrease the risk of data loss.”

You perform a partial differential backup using a BACKUP statement. The statement should include both the DIFFERENTIAL option and also the READ_WRITE_FILEGROUPS option. If the base includes any read-only file or filegroups, each one should be individually listed in the statement.

According to SQL Server database experts Idera, you should consider using partial backups or partial differential backups if 1) the size of database size makes full or differential backups difficult and time consuming, 2) the database can only be down for a very short period of time, or 3) you have particular database files that are frequently corrupted, are mission-critical or are constantly modified.