Share via

  • Article

Differential File Backups

 This topic is relevant only for SQL Server databases that contain multiple filegroups.

Note

A differential file backup requires a full file backup as a base. For more information about full file backups, see Full File Backups.

Differential file backups provide a quick and space-saving way to create current file backups. Under the simple recovery model, differential file backups are enabled only for read-only filegroups. Under the full recovery model, differential file backups are allowed on any filegroup for which you have a differential base. Using differential file backups can significantly reduce recovery time by reducing the amount of transaction log that must be restored.

Consider using differential file backups for the following situations:

  • Some files are backed up less frequently than other files.

  • Files are large, and the data is updated infrequently; or the same data is updated repeatedly.

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

Important

Avoid using both database differential and differential file backups on the same database.

Differential Backups of Read-Only Filegroups in Read/Write Databases

Differential backups of a read/write database operate correctly even if one of the filegroups was read-only when the differential base was created. As with any differential backup of a read/write database, the SQL Server Database Engine records the differential base in the primary file.

Note

For a read-only database, the differential bitmap of the primary file cannot be updated during a backup. For more information, see Backing Up Read-Only Databases).

Backing Up a Read-Mostly Filegroup

In a read-mostly filegroup, most of the time the filegroup is read-only; but, occasionally, the filegroup is set to read/write for brief maintenance periods. For example, a usually read-only filegroup might be set temporarily to read/write to allow for the files to be bulk imported, and then reset to read-only.

After the updates are finished, you can protect the new data by backing up the filegroup. The best practice for this backup depends on how much the file has changed:

  • If the filegroup has changed significantly. and the filegroup is still read/write, take a full file backup. Because the filegroup is currently read/write, the backup operation can reset the differential bitmap to prepare for a new series of differential backups. Then, change the filegroup back to read-only and immediately create a differential file backup to allow you to restore the now read-only filegroup.

  • If the filegroup has not changed ,much since its last full file backup, assuming that a full file backup exists as a differential base, set the filegroup back to read-only immediately, and then create a differential file backup.

Note

The IsReadOnly property is set on a filegroup, not individual files. If a filegroup is read-only (that is, if the IsReadOnly property is true for the filegroup), all the files in the filegroup are read-only.

Creating a Differential File Backup

The required BACKUP syntax for creating a differential file backup is:

BACKUP DATABASE database_name <file_or_filegroup> [ ,...n] TO <backup_device> WITH DIFFERENTIAL

To create a differential file backup

Note

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

Note

File backups are not supported by the Maintenance Plan Wizard.