Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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 A and 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 A and C current 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 A, B, and C are recovery pending, and the filegroups are offline.
Online restore of filegroups A and C.
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 A and C immediately.
RESTORE DATABASE adb FILEGROUP='A', FILEGROUP='C' WITH RECOVERY
At this point the primary and filegroups A and C are online. Files in filegroup B remain recovery pending, with the filegroup offline.
Online restore of filegroup B.
Files in filegroup B are restored any time thereafter.
The backup of filegroup B was 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.