Edit

Share via

File Restores (Simple Recovery Model)

Applies to: SQL Server

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.

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.

    All editions of SQL Server support offline file restore.

  • Online file restore

    In an online file restore, if database is online at restore time, it remains online during the file restore. However, each filegroup in which a file is being restored is offline during the restore operation. After all the files in an offline filegroup are recovered, the filegroup is automatically brought online.

    For information about support for online page and file restore, see Database Engine Features and Tasks. For more information about online restores, see Online Restore (SQL Server).

    Tip

    If you want the database to be 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.

In this Topic:

Overview of File and Filegroup Restore Under the Simple Recovery Model

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

  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.

Transact-SQL Steps for File Restore Sequence (Simple Recovery Model)

This section shows the essential Transact-SQLRESTORE options for a simple file-restore sequence. Syntax and details that are not relevant to this purpose are omitted.

The restore sequence contains only two Transact-SQL statements. The first statement restores a secondary file, file A, which is restored using WITH NORECOVERY. The second operation restores two other files, B and C which are restored using WITH RECOVERY from a different backup device:

  1. RESTORE DATABASE database FILE =name_of_file_A

    FROM file_backup_of_file_A

    WITH NORECOVERY**;**

  2. RESTORE DATABASE database FILE =name_of_file_B,name_of_file_C

    FROM file_backup_of_files_B_and_C

    WITH RECOVERY**;**

Examples

Related Tasks

To restore files and filegroups

See Also

Backup and Restore: Interoperability and Coexistence (SQL Server)
Differential Backups (SQL Server)
Full File Backups (SQL Server)
Backup Overview (SQL Server)
Restore and Recovery Overview (SQL Server)
RESTORE (Transact-SQL)
Complete Database Restores (Simple Recovery Model)
Piecemeal Restores (SQL Server)