Working with Multibase Differential Backups

Icon showing a blue database disk This topic is relevant only for full recovery model or bulk-logged recovery model databases in which multiple filegroups exist and for which the backup strategy includes file backups and differential backups. Differential partial backups must use a single partial backup as the base.

Note

Under the simple recovery model, a differential backup must have one base. Multibase differential backups are prohibited, and trying to use a multibase differential backup displays an error and fails.

Under the full recovery model a differential backup can include files that have different differential bases. A differential with multiple bases is known as a multibase differential backup. Multibase differential backups can be difficult to administer and maintain. Therefore, we recommend that you avoid creating differential backups that have multiple bases. Instead, use differential database backups with database backups and differential file backups with file backups.

Causes of Multibase Differential Backups

Multibase differential backups can occur from taking a differential database backup that is based on a set of file backups. A multibase differential can occur from using a file differential that lists multiple files, where the list differs from that supplied to the most recent file backup. Multiple bases can also occur when access for a filegroup has been changed since the partial backup. For more information, see Differential Partial Backups.

When a Multibase Differential Might Be Useful

In rare situations, a multibase differential may make sense. For example, a database administrator might use a full database differential with a full set of file backups. A whole set of file backups replaces a single database backup, creating a multibase differential. Depending on database usage, the differential might be fairly small. In any case, restoring a file follows the same procedure: Restore the file backup first and then the full database differential.

In this case, a multibase differential is easier to manage than several separate differential file backups; and the backup and restore steps remain simple. There is no confusion over which backups to restore, because all files are treated the same.

If you use multiple bases for differential backups, keep the multibase strategy as simple as possible. Avoid strategies that use complex multibase differentials, especially ones that require that some files or filegroups be treated differently from other files or filegroups at restore time. For example, avoid intermixing full database backups, file backups, and database differentials. Also, avoid grouping files differently in the full database backup and the differential backups, unless the groupings are very simple and predictable, as in the previous example.

See Also

Concepts

Base of a Differential Backup
Differential File Backups
Differential Database Backups
Differential Partial Backups

Other Resources

Using Differential Backups

Help and Information

Getting SQL Server 2005 Assistance