Example: Online restore of a read-write file (full recovery model)
Applies to: SQL Server
This article 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 isn't 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
a1is in the
RESTORINGstate, and filegroup
After you restore the file, take 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.
You restore all the log backups taken since the restored file backup, ending with the latest log backup (
log_backup3, taken in the previous step). 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.
- Example: Piecemeal Restore of Database (Simple Recovery Model)
- Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)
- Example: Online Restore of a Read-Only File (Simple Recovery Model)
- Example: Piecemeal Restore of Database (Full Recovery Model)
- Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model)
- Example: Online Restore of a Read-Only File (Full Recovery Model)