Example: Online Restore of a Read-Write File (Full Recovery Model)
THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
This topic is relevant for SQL Server databases under the full recovery model that contain multiple files or filegroups.
In this example, a database named
adb, which uses the full recovery model, contains three filegroups. Filegroup
A is read/write, and filegroup
B and filegroup
C are read-only. Initially, all of the filegroups are online.
a1 in filegroup
A appears to be damaged, and the database administrator decides to restore it while the database remains online.
Under the simple recovery model, online restore of read/write data is not allowed.
The syntax for an online restore sequence is the same as for an offline restore sequence.
Online restore of file
RESTORE DATABASE adb FILE='a1' FROM backup WITH NORECOVERY;
At this point, file a1 is in the RESTORING state, and filegroup A is offline.
After restoring the file, the database administrator takes a new log backup to make sure that the point at which the file went offline is captured.
BACKUP LOG adb TO log_backup3;
Online restore of log backups.
The administrator restores all the log backups taken since the restored file backup, ending with the latest log backup (log_backup3, taken in step 2). After the last backup is restored, the database is recovered.
RESTORE LOG adb FROM log_backup1 WITH NORECOVERY; RESTORE LOG adb FROM log_backup2 WITH NORECOVERY; RESTORE LOG adb FROM log_backup3 WITH NORECOVERY; RESTORE LOG adb WITH RECOVERY;
a1is now online.