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

  • Article

Icon showing a blue database disk 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

  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 filegroup B 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.

See Also

Concepts

Performing Piecemeal Restores

Other Resources

BACKUP (Transact-SQL)
Examples of Restore Sequences for Several Restore Scenarios
RESTORE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance