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 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.

  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

• 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)

Advanced topics

• Controlling Creation of Added Files During Roll Forward
• Restoring Renamed Files and Filegroups

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

• Example: Online Restore of a Read-Only File (Simple Recovery Model)
• Example: Offline Restore of Primary and One Other Filegroup (Full Recovery Model)

See Also

Concepts

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

Other Resources

Backup and Restore Considerations for Related Features
Designing Files and Filegroups
RESTORE (Transact-SQL)
Working with Restore Sequences for SQL Server Databases

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Incorporated the introduction to file restore from the former "Restore Scenarios Under the Simple Recovery Model" topic.