Edit

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

  • Article

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

See Also

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