Export (0) Print
Expand All
3 out of 4 rated this helpful - Rate this topic

Partial Backups

Updated: 17 July 2006

Icon showing a blue database disk 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.

ms191539.note(en-US,SQL.90).gifNote:
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.

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>

ms191539.note(en-US,SQL.90).gifNote:
Partial backups are not supported by SQL Server Management Studio or the Maintenance Plan Wizard.

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.

Release History

17 July 2006

New content:
  • Added summary of the basic BACKUP syntax for creating a partial backup.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.