Export (0) Print
Expand All

Example: Online Restore of a Read/Write File (Full Recovery Model)

Icon showing a blue database disk 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.

ms175532.note(en-US,SQL.90).gifNote:
Under the simple recovery model, online restore of read/write data is not allowed.

  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_backup WITH COPY_ONLY
    
  3. 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
    
    File a1 is now online.

Community Additions

ADD
Show:
© 2015 Microsoft