Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

Article
06/13/2017

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 C; therefore, B will be restored last.

Restore Sequences

Note

The syntax for an online restore sequence is the same as for an offline restore sequence.

Partial restore of the primary and filegroup A from a partial backup.
```
RESTORE DATABASE adb READ_WRITE_FILEGROUPS FROM partial_backup   
WITH PARTIAL, RECOVERY  
```
At this point the primary filegroup and filegroup A are online. Files in filegroups B and C are recovery pending, and the filegroups are offline.
Online recovery of filegroup C.

Filegroup C is consistent because the partial backup that was restored above was taken after filegroup C became 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 A and C are online. Files in filegroupB remain recovery pending, with the filegroup offline.
Online restore of filegroup B.

Files in filegroup B must be restored. The database administrator restores the backup of filegroup B taken after filegroup B became read-only and before the partial backup.
```
RESTORE DATABASE adb FILEGROUP='B' FROM backup   
WITH RECOVERY  
```
All filegroups are now online.

Share via

Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

Restore Sequences

Additional Examples

See Also

Additional resources