File Restores (Simple Recovery Model)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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 2016 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 Features Supported by the Editions of SQL Server 2016. For more information about online restores, see Online Restore (SQL Server).
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:
A file restore scenario consists of a single restore sequence that copies, rolls forward, and recovers the appropriate data as follows:
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.
Transact-SQL Steps for File Restore Sequence (Simple Recovery Model)
This section shows the essential Transact-SQL RESTORE 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:
RESTORE DATABASE database FILE = name_of_file_A
RESTORE DATABASE database FILE = name_of_file_B, name_of_file_C
To restore files and filegroups