• Article

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.

Note

For more information, see Performing a Complete Database Restore (Simple Recovery Model) or Performing Piecemeal Restores.

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

    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

  1. Restore each damaged file from its most recent file backup.

  2. Restore the most recent differential file backup for each restored file, and recover the database.

To restore files and filegroups

Advanced topics

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, B and 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

Note

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.

Examples