Example: Online Restore of a Read-Only File (Simple Recovery Model)

This topic is relevant for SQL Server databases under the simple recovery model that contain a read-only filegroup. Under the simple recovery model, a read-only file can be restored online if a file backup exists that was taken since the file became read-only for the last time.

In this example, a database named adb contains three filegroups. Filegroup A is read/write, and filegroups B and C are read-only. Initially, all of the filegroups are online. A read-only file in filegroup B, b1, has to be restored. The database administrator can restore it by using a backup that was taken after the file became read-only. For the duration of the restore, filegroup B will be offline, but the remainder of the database will remain online.

Restore Sequence

To restore the file, the database administrator uses the following restore sequence:

RESTORE DATABASE adb FILE='b1' FROM filegroup_B_backup 
WITH RECOVERY

The file is now online.

Additional Examples

• Example: Piecemeal Restore of Database (Simple Recovery Model)

• Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

• Example: Piecemeal Restore of Database (Full Recovery Model)

• Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model)

• Example: Online Restore of a Read/Write File (Full Recovery Model)

• Example: Online Restore of a Read-Only File (Full Recovery Model)

See Also

Reference

RESTORE (Transact-SQL)

Concepts

Online Restore (SQL Server)

Piecemeal Restores (SQL Server)

File Restores (Simple Recovery Model)

Restore and Recovery Overview (SQL Server)