THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
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 2017 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.
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:
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-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,
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
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)
Complete Database Restores (Simple Recovery Model)
Piecemeal Restores (SQL Server)