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

 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.

Restore Sequences:

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

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

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

4. Online restore of filegroup B.
   Files in filegroup B are restored any time thereafter.

   Note

   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.

See Also

Concepts

Applying Transaction Log Backups
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