Share via

  • Article

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.

Note

Under the simple recovery model, online restore of read/write data is not allowed.

Restore Sequences

  1. 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.

  2. 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;

  3. 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.

Change History

Updated content

Clarified the example.