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

Applies to: SQL Server

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:

Note

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

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 log_backup1 WITH NORECOVERY  
   RESTORE LOG adb FROM log_backup2 WITH NORECOVERY  
   RESTORE LOG adb FROM log_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.

Additional Examples

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

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

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

• Example: Piecemeal Restore of Database (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

BACKUP (Transact-SQL)
Online Restore (SQL Server)
Apply Transaction Log Backups (SQL Server)
RESTORE (Transact-SQL)
Piecemeal Restores (SQL Server)