Partial Backups

This topic is relevant for all SQL Server databases.

Partial backups are new in SQL Server 2005. 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. However, partial backups work on all databases, regardless of recovery model.

Note

For an overview of the various types of backups, see either Backup Under the Simple Recovery Model or Backup Under the Full Recovery Model.

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

Creating Partial Backups

To create a partial backup, use the BACKUP statement and specify the READ_WRITE_FILEGROUPS option. You can also specify any read-only file or filegroup to include it in a partial backup. The required syntax for creating a partial backup is:

BACKUP DATABASE database_name READ_WRITE_FILEGROUPS [ ,<file_filegroup_list> ] TO <backup_device>

Note

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

Using Differential Partial Backups

A partial backup can serve as the differential base for differential partial backups. This let you supplement a partial backup with a short series of differential partial backups. For more information, see Using Differential Backups and Differential Partial Backups.

See Also

Concepts

Backing Up Read-Only Databases
Backup Under the Full Recovery Model
Backup Under the Simple Recovery Model
Security Considerations for Backup and Restore
Read-Only Filegroups and Compression

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	New content: Added summary of the basic BACKUP syntax for creating a partial backup.