Example: Online Restore of a Read-Write File (Full Recovery Model)
Applies to: SQL Server (all supported versions)
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 DATABASE adb WITH RECOVERY;
a1is now online.