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

Applies to: SQL Server

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.

1. 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.

2. 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.

3. 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.

Additional Examples

• Example: Piecemeal Restore of Database (Simple Recovery Model)

• Example: Online Restore of a Read-Only File (Simple Recovery Model)

• Example: Piecemeal Restore of Database (Full Recovery Model)

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

• Example: Online Restore of a Read-Write File (Full Recovery Model)

• Example: Online Restore of a Read-Only File (Full Recovery Model)

See Also

Online Restore (SQL Server)
BACKUP (Transact-SQL)
RESTORE (Transact-SQL)
Piecemeal Restores (SQL Server)