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 Edition, SQL Server 2005 Express Edition, and SQL Server 2005 Workgroup Edition, support only offline restore, and restoring a file to the primary filegroup always requires that the database be offline. SQL Server 2005 Enterprise Edition uses offline restore if the database is already offline.
In SQL Server 2005 Enterprise Edition, 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, 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.
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
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.
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.
Restore each damaged file from the most recent file backup of that file.
Restore the most recent differential file backup, if any, for each restored file.
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.
Recover the database.
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
- How to: Restore Files and Filegroups (Transact-SQL)
- How to: Restore Files and Filegroups over Existing Files (Transact-SQL)
- How to: Restore Files and Filegroups (SQL Server Management Studio)
- SqlRestore (SMO)
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,
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
- Example: Online Restore of a Read/Write File (Full Recovery Model)
- Example: Online Restore of a Read-Only File (Full Recovery Model)
- Example: Offline Restore of Primary and One Other Filegroup (Full Recovery Model)
Differential Backups and New Files
Placing Indexes on Filegroups
Differential File Backups
Understanding How Restore and Recovery of Backups Work in SQL Server
Using Files and Filegroups to Manage Database Growth
Using Files and Filegroups
Applying Transaction Log Backups
Backup Overview (SQL Server)
Overview of Restore and Recovery in SQL Server
Designing Files and Filegroups
Backup and Restore Considerations for Related Features
Restoring a Database to a Point Within a Backup
Working with Restore Sequences for SQL Server Databases
Help and Information
17 July 2006