Example: Piecemeal Restore of Database (Simple Recovery Model)
Applies To: SQL Server 2016
THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, starting with the primary and all read/write, secondary filegroups.
In this example, database
adb is restored to a new computer after a disaster. The database is using the simple recovery model. Before the disaster, all the filegroups are online. Filegroups
C are read/write, and filegroup
B is read-only. Filegroup
B became read-only before the most recent partial backup, which contains the primary filegroup and the read/write secondary filegroups,
C. After filegroup
B became read-only, a separate file backup of filegroup
B was taken.
Partial restore of the primary and filegroups
RESTORE DATABASE adb FILEGROUP='A',FILEGROUP='C' FROM partial_backup WITH PARTIAL, RECOVERY;
At this point, the primary and filegroups
Care online. All files in filegroup
Bare recovery pending, and the filegroup is offline.
Online restore of filegroup
RESTORE DATABASE adb FILEGROUP='B' FROM backup WITH RECOVERY;
All filegroups are now online.