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

See Also

Concepts

Performing Online Restores
Performing Piecemeal Restores
Overview of Restore and Recovery in SQL Server
Applying Transaction Log Backups

Other Resources

BACKUP (Transact-SQL)
Examples of Restore Sequences for Several Restore Scenarios
RESTORE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance