| title | Partial Backups (SQL Server) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 03/14/2017 | ||||
| ms.prod | sql-non-specified | ||||
| ms.prod_service | database-engine | ||||
| ms.service | |||||
| ms.component | backup-restore | ||||
| ms.reviewer | |||||
| ms.suite | sql | ||||
| ms.technology |
|
||||
| ms.tgt_pltfrm | |||||
| ms.topic | article | ||||
| helpviewer_keywords |
|
||||
| ms.assetid | fe6b6bb1-38d0-46c4-bab8-31df14e8999c | ||||
| caps.latest.revision | 46 | ||||
| author | JennieHubbard | ||||
| ms.author | jhubbard | ||||
| manager | jhubbard | ||||
| ms.workload | On Demand |
[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md] 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)