| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Partial Backups (SQL Server) |
A partial backup in SQL Server contains the data in the primary filegroup, all read-write filegroups, and, optionally, one or more read-only files. |
MashaMSFT |
mathoma |
03/14/2017 |
sql |
backup-restore |
conceptual |
|
[!INCLUDE SQL Server] All [!INCLUDEssNoVersion] recovery models support partial backups, so this topic is relevant for all [!INCLUDEssNoVersion] 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).
Note
Partial backups are not supported by [!INCLUDEssManStudioFull] 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)
Backup Overview (SQL Server)
File Restores (Simple Recovery Model)
Piecemeal Restores (SQL Server)