Share via

  • Article

Planning a Piecemeal Restore Sequence for a File in the Restoring, Recovery-Pending, or Offline State

 This topic is relevant only for SQL Server databases that contain multiple filegroups (and, under the simple model, only for read-only filegroups) when you are planning a piecemeal recovery of a database.

If a restore sequence involves a file in the restoring, recovery-pending, or offline state, you might be able to recover the file without restoring its data. To determine whether you must restore a full backup of the file or can simply recover the file, you can use metadata stored in the sys.database_files and sys.master_files catalog views.

Redo LSNs

The first step is to inspect the catalog view columns that contain the redo LSNs: redo_start_lsn, redo_start_fork_guid, redo_target_lsn, and redo_target_fork_guid. The following table describes the redo LSNs and describes how to interpret them.

Columns

Description

redo_start_lsn and redo_start_fork_guid

Together, these columns describe an (lsn,guid) pair that represents the point in time of the file. As the file is rolled forward, the values of these columns change. Roll forward continues from this point.

Important noteImportant
If redo_start_lsn = NULL, the on-disk state of the file is unknown, and the file must be restored from a full backup.

redo_target_lsn and redo_target_fork_guid

Together, these columns describe an (lsn,guid) pair that defines the recovery point to which the file must be restored to be consistent with the online database (the target recovery point).

Deciding Whether to Use sys.database_files or sys.master_files

The sys.database_files and sys.master_files catalog views both contain the redo LSN columns, but these views are not always consistent. Generally, if the database is online, the values in sys.database_files and sys.master_files are consistent. However, the values will be inconsistent in the following situations:

  • If the database is read-only, sys.database_files is not updated with any changes that are caused by the backup, and only sys.master_files contains up-to-date information.

    Note

    To find out whether a file is read only, examine the is_read_only and read_only_lsn columns. is_read_only indicates whether the file is read-only. If so, read_only_lsn is the point at which the file became read-only.

  • If the database is offline (for example, when it is being restored), the database catalog is inaccessible. For an offline database, you must use sys.master_files to obtain information.

  • If a restore operation is currently affecting the file, the redo LSNs of the file are being updated and are inconsistent. You should examine the redo LSN columns only between restores.

Interpretation of These Columns

Note

This section assumes that you are familiar with the concepts recovery path and recovery fork. For more information, see Recovery Paths.

This section is relevant only if you have performed point-in time-recovery and you still have backups from any defunct recovery paths. When you are restoring a file in the restoring, recovery-pending, or offline state, recovery forks are relevant. By analyzing the recovery forks, you can identify the potential recovery paths. Generally, one recovery path will clearly be best for recovering the database.

To identify the best recovery path, you have to find out whether the file is on the target recovery fork or is on a different recovery fork:

  • The file is on a different recovery fork.

    If redo_start_fork_guid != redo_target_fork_guid and is not an ancestor of redo_target_fork_guid, the file is on a different recovery fork from the target fork.

    Note

    To locate an ancestor fork, follow the log chain backwards. For more information, see Recovery Paths.

    In this case, the file must be restored from a full backup. This restore will position the file at a point that is a valid ancestor of the current recovery point of the database.

    Note

    To restore any file, the file backup must be an ancestor of the recovery point of the database. Always look for the most recent full backup of the file. The data must be rolled forward to the target point. The only exception is that a file backup of a read-only file does not have to be rolled forward if the file has been read-only since before the backup. If it is necessary, after you restore the file backup, restore a differential file backup, if any, and log backups to bring the file to the target recovery point.

  • The file is on the current (target) recovery fork or is an ancestor of the target fork.

    Note

    If you have taken a backup of the file since recovery of the database, the file is on the target recovery fork.

    In these cases, whether the file must be restored depends on the relationship of redo_start_lsn to redo_target_lsn, as described in the table below.

    If...

    Then...

    redo_start_lsn =redo_target_lsn

    The file does not have to be restored.

    The file is consistent with the database and can be brought online without using RESTORE DATABASE database_name WITH RECOVERY.

    redo_start_lsn <redo_target_lsn

    Before the file can be brought online, roll forward must reach redo_target_lsn.

    redo_start_lsn >redo_target_lsn

    The database is earlier than the file. The file must be restored from a full backup (or the database may be re-restored to a later point in time with another partial restore sequence).

    NoteNote
    This situation can occur only for an offline restore, because as soon as the primary filegroup is recovered, a new recovery fork is generated. Any unrecovered secondary filegroups are no longer on the same recovery path as the primary filegroup.

Note

After you restore backups for one of these recovery paths, the alternative recovery paths are no longer valid. Backups that are specific to a invalid recovery path become defunct. A best practice is to delete defunct backups, or set them aside and clearly mark them as defunct.