Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)
THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
This topic is relevant for SQL Server databases under the simple recovery model that contain a read-only filegroup.
A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, beginning with the primary and all read/write, secondary filegroups.
In this example, a database named
adb, which uses the simple recovery model, contains three filegroups. Filegroup
A is read/write, and filegroup
B and filegroup
C are read-only. Initially, all of the filegroups are online.
The primary and filegroup
B of database
adb appear to be damaged; therefore, the database administrator decides to restore them by using a piecemeal restore sequence. Under the simple recovery model, all read/write filegroups must be restored from the same partial backup. Although filegroup
A is intact, it must be restored with the primary filegroup to make sure that they are consistent (the database will be restored to the point in time defined by the end of the last partial backup). Filegroup
C is intact, but it must be recovered to bring it online. Filegroup
B, although damaged, contains less critical data than Filegroup
B will be restored last.
The syntax for an online restore sequence is the same as for an offline restore sequence.
Partial restore of the primary and filegroup
Afrom a partial backup.
RESTORE DATABASE adb READ_WRITE_FILEGROUPS FROM partial_backup WITH PARTIAL, RECOVERY
At this point the primary filegroup and filegroup
Aare online. Files in filegroups
Care recovery pending, and the filegroups are offline.
Online recovery of filegroup
Cis consistent because the partial backup that was restored above was taken after filegroup
Cbecame read-only, although the database was taken back in time by the restore. The database administrator recovers the filegroup
C, without restoring it, to bring it online.
RESTORE DATABASE adb FILEGROUP='C' WITH RECOVERY
At this point the primary and filegroups
Care online. Files in filegroupB remain recovery pending, with the filegroup offline.
Online restore of filegroup
Files in filegroup
Bmust be restored. The database administrator restores the backup of filegroup
Btaken after filegroup
Bbecame read-only and before the partial backup.
RESTORE DATABASE adb FILEGROUP='B' FROM backup WITH RECOVERY
All filegroups are now online.