Example: Online Restore of a Read/Write File (Full Recovery Model)
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.
File 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.
Online restore of file a1.
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;
File a1 is now online.