Partial Backups (SQL Server)

Applies to: SQL Server

All SQL Server recovery models support partial backups, so this topic is relevant for all SQL Server databases. However, partial backups are designed for use under the simple recovery model to improve flexibility for backing up very large databases that contain one or more read-only filegroups.

Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.

Note

If a read-only database is changed to read/write after a partial backup, there might be read/write secondary filegroups that are not in the partial backup. In this case, if you try to take a differential partial backup, the backup fails. Before you can take a differential partial backup of the database, you must take another partial backup. The new partial backup contains every read/write secondary filegroup and can serve as the base for differential partial backups.

File backups of read-only filegroups can be combined with partial backups. For information about file backups, see Full File Backups (SQL Server).

A partial backup can serve as the differential base for differential partial backups. For more information, see Differential Backups (SQL Server).

Related Tasks

Note

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

To create a partial backup

• BACKUP (Transact-SQL) (READ_WRITE_FILEGROUPS; FILEGROUP option, if needed)

To use a partial backup in a restore sequence

• Example: Piecemeal Restore of Database (Simple Recovery Model)

• Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

See Also

Backup Overview (SQL Server)
File Restores (Simple Recovery Model)
Piecemeal Restores (SQL Server)