Performing File Restores (Simple Recovery Model)
This topic is relevant only for simple-model databases that contain at least one read-only secondary filegroup.
In a file restore, the goal is to restore one or more damaged files without restoring the whole database. Under the simple recovery model, file backups are supported only for read-only files. The primary filegroup and read/write secondary filegroups are always restored together, by restoring a database or partial backup.
For more information, see Performing a Complete Database Restore (Simple Recovery Model) or Performing Piecemeal Restores.
All editions of SQL Server 2005 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.
The 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.
When the database is online, its primary filegroup and all its read/write filegroups are all online. 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.
Restoring Files or Filegroups
A file restore scenario consists of a single restore sequence that copies, rolls forward, and recovers the appropriate data as follows:
To restore a damaged file or files from file backups and differential file backups
Restore each damaged file from its most recent file backup.
Restore the most recent differential file backup for each restored file, and recover the database.
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 File Restore (Simple Recovery Model)
The following Transact-SQL code shows the critical RESTORE options in a restore sequence for the file restore scenario. Syntax and details that are not relevant to this purpose are omitted.
This restore sequence contains two operations. The first restores a secondary file, file
A, which is restored
with NORECOVERY. The second operation restores two other files,
C, which are restored
with RECOVERY from a different backup device.
The file-restore sequence is as follows:
RESTORE DATABASE <database> FILE = <name_of_file_A> FROM <file_backup_of_file_A> WITH NORECOVERY RESTORE DATABASE <database> FILE=<name_of_file_B>, <name_of_file_C> FROM <file_backup_of_files_B_and_C> WITH RECOVERY
If you are using SQL Server 2005 Enterprise Edition and want the database offline for a file restore, take the database offline before you start the restore sequence by executing the following ALTER DATABASE statement:
ALTER DATABASE <database_name> SET OFFLINE.
- Example: Online Restore of a Read-Only File (Simple Recovery Model)
- Example: Offline Restore of Primary and One Other Filegroup (Full Recovery Model)
Differential Backups and New Files
Differential File Backups
Full File Backups
Backup Overview (SQL Server)
Overview of Restore and Recovery in SQL Server
Understanding How Restore and Recovery of Backups Work in SQL Server
Using Files and Filegroups to Manage Database Growth
Using Files and Filegroups
Help and Information
17 July 2006