Share via

  • Article

Performing File Restores (Full Recovery Model)

This topic is relevant only for databases that contain multiple files or filegroups under the full or bulk-load recovery model.

In a file restore, the goal is to restore one or more damaged files without restoring the whole database. All editions of SQL Server support restoring files when the database is offline (offline page restore). SQL Server 2005 Standard, SQL Server 2005 Express Edition, and SQL Server 2005 Workgroup, and later versions, support only offline restore, and restoring a file to the primary filegroup always requires that the database be offline. SQL Server 2005 Enterprise Edition and later versions use offline restore if the database is already offline.

In SQL Server 2005 Enterprise Edition and later versions, if the database is online during a file restore, the database remains online. Restoring and recovering a file while the database is online is called an online file restore. 

These file restore scenarios are as follows:

  • Offline file restore

    In an offline file restore, the database is offline while damaged files or filegroups are restored. At the end of the restore sequence, the database comes online.

  • Online file restore

    In SQL Server 2005 Enterprise Edition and later versions, file restores are automatically performed online when the database is online. However, any filegroup in which a file is being restored is offline. After all the files in an offline filegroup are recovered, the filegroup is automatically brought online. For more information about online restores, see Performing Online Restores. 

    Note

    Only online filegroups can be queried or updated. An attempt to access a filegroup that is offline, including a filegroup that contains a file that is being restored or recovered, causes an error.

If the filegroup that is being restored is read/write, an unbroken chain of log backups must be applied after the last data or differential backup is restored. This brings the filegroup forward to the log records in the current active log records in the log file. The recovery point is typically near the end of log, but not necessarily.

If the filegroup that is being restored is read-only, usually applying log backups is unnecessary and is skipped. If the backup was taken after the file became read-only, that is the last backup to restore. Roll forward stops at the target point.

Restoring Files or Filegroups

To restore a damaged file or files from file backups and differential file backups

  1. Create a tail-log backup of the active transaction log.

    If you cannot do this because the log has been damaged, you must restore the whole database. For information about how to back up a transaction log, see Creating Transaction Log Backups.

    Important

    For an offline file restore, you must always take a tail-log backup before the file restore. For an online file restore, you must always take the log backup after the file restore. This log backup is necessary to allow for the file to be recovered to a state consistent with the rest of the database.

  2. Restore each damaged file from the most recent file backup of that file.

  3. Restore the most recent differential file backup, if any, for each restored file.

  4. Restore transaction log backups in sequence, starting with the backup that covers the oldest of the restored files and ending with the tail-log backup created in step 1.

    You must restore the transaction log backups that were created after the file backups to bring the database to a consistent state. The transaction log backups can be rolled forward quickly, because only the changes that apply to the restored files are applied. Restoring individual files can be better than restoring the whole database, because undamaged files are not copied and then rolled forward. However, the whole chain of log backups still has to be read.

  5. Recover the database.

Note

File backups can be used to restore the database to an earlier point in time. To do this, you must restore a complete set of file backups, and then restore transaction log backups in sequence to reach a target point that is after the end of the most recent restored file backup. For more information about point-in-time recovery, see Restoring a Database to a Point Within a Backup.

To restore files and filegroups

Advanced topics

Transact-SQL Restore Sequence for Offline File Restore (Full Recovery Model)

A file restore scenario consists of a single restore sequence that copies, rolls forward, and recovers the appropriate data.

The following Transact-SQL code shows the critical RESTORE options in a restore sequence for the file restore scenario. Syntax and details not relevant to this purpose are omitted.

The example shows an offline restore of two secondary files, A and B, with NORECOVERY. Next, two log backups are applied with NORECOVERY, followed with the tail-log backup, and this is restored with RECOVERY. The example starts by taking the file offline, for an offline file restore.

--Take the file offline.
ALTER DATABASE database_name MODIFY FILE SET OFFLINE
-- Back up the currently active transaction log.
BACKUP LOG database_name
   TO <tail_log_backup>
   WITH NORECOVERY
GO 
-- Restore the files.
RESTORE DATABASE database_name FILE=<name> 
   FROM <file_backup_of_file_A> 
   WITH NORECOVERY
RESTORE DATABASE database_name FILE=<name> ......
   FROM <file_backup_of_file_B> 
   WITH NORECOVERY
-- Restore the log backups.
RESTORE LOG database_name FROM <log_backup> 
   WITH NORECOVERY
RESTORE LOG database_name FROM <log_backup> 
   WITH NORECOVERY
RESTORE LOG database_name FROM <tail_log_backup> 
   WITH RECOVERY