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.
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
RESTORE DATABASE adb FILE='a1' FROM backup WITH NORECOVERY
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_backup WITH COPY_ONLY
Online restore of log backups.
RESTORE LOG adb FROM log_backup WITH NORECOVERY RESTORE LOG adb FROM log_backup WITH NORECOVERY RESTORE LOG adb FROM log_backup WITH RECOVERY
a1is now online.
ConceptsPerforming Online Restores
Performing Piecemeal Restores
Overview of Restore and Recovery in SQL Server
Applying Transaction Log Backups
Other ResourcesBACKUP (Transact-SQL)
Examples of Restore Sequences for Several Restore Scenarios