Example: Piecemeal Restore of Only Some Filegroups (Full 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 full recovery model that contain multiple files or filegroups.
A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, starting with the primary and all read/write, secondary filegroups.
In this example, a database named
adb, which uses the full 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. The primary filegroup is fairly small and can be restored quickly. The database administrator decides to restore them by using a piecemeal restore sequence. First, the primary filegroup and the subsequent transaction logs are restored the database is recovered.
The intact filegroups
C contain critical data. Therefore, they will be recovered next to bring them online as quickly as possible. Finally, the damaged secondary filegroup,
B, is restored and recovered.
The syntax for an online restore sequence is the same as for an offline restore sequence.
Create a tail log backup of database
adb. This step is essential to make the intact filegroups
Ccurrent with the recovery point of the database.
BACKUP LOG adb TO tailLogBackup WITH NORECOVERY
Partial restore of the primary filegroup.
RESTORE DATABASE adb FILEGROUP='Primary' FROM backup WITH PARTIAL, NORECOVERY RESTORE LOG adb FROM backup1 WITH NORECOVERY RESTORE LOG adb FROM backup2 WITH NORECOVERY RESTORE LOG adb FROM backup3 WITH NORECOVERY RESTORE LOG adb FROM tailLogBackup WITH RECOVERY
At this point the primary is online. Files in filegroups
Care recovery pending, and the filegroups are offline.
Online restore of filegroups
Because their data is undamaged, these filegroups do not have to be restored from a backup, but they do have to be recovered to bring them online.
The database administrator recovers
RESTORE DATABASE adb FILEGROUP='A', FILEGROUP='C' WITH RECOVERY
At this point the primary and filegroups
Care online. Files in filegroup
Bremain recovery pending, with the filegroup offline.
Online restore of filegroup
Files in filegroup
Bare restored any time thereafter.
The backup of filegroup
Bwas taken after the filegroup became read-only; therefore, these files do not have to be rolled forward.
RESTORE DATABASE adb FILEGROUP='B' FROM backup WITH RECOVERY
All filegroups are now online.