Previous Topic: Restore by Tree Using a Separate Job for Each SessionNext Topic: Perform an Online Torn Page Restore Using Microsoft SQL Server 2000, 2005, 2008, or 2012 Enterprise, Data Center, or Developer Editions

Restoring Microsoft SQL Server Databases › Perform an Offline Torn Page Restore Using Microsoft SQL Server 2000, 2005, 2008, or 2012

Perform an Offline Torn Page Restore Using Microsoft SQL Server 2000, 2005, 2008, or 2012

Microsoft SQL Server 2000, 2005, 2008, and 2012 have the ability to detect when the data in the database has been damaged and isolate the damage at the data page level. You can find the current list of known damaged pages in the [suspect_pages] table of system database [msdb], at any point of time. In addition to torn page detection and isolation, SQL 2005 also introduces the ability to perform a restore in which only those data pages which are damaged are overwritten. This will allow you to bring a database which is slightly damaged back into operation quickly.

Note: Do NOT take the database offline before you start this procedure.

Follow these steps:

  1. Change to Full Recovery Model, if the database is using the Simple Recovery Model.
  2. (Optional) Perform a Database Consistency Check (DBCC CheckDB) on the database to locate any additional damaged pages beyond the one already reported. This can be done as part of step #4.
  3. Disconnect all clients using the database. (Otherwise, the following step will fail).
  4. Perform a Transaction Log backup with the Backup only the log tail and leave the database in an unrecovered mode (Log Tail) option. If you have not performed step 2 separately, you should also select the Database Consistency Check Before Backup option and Continue Backup if DBCC Fails option.
  5. Perform an Offline Torn Page Repair Restore of the database as follows:
    1. Open the Restore Manager.
    2. On the Source tab, use the Restore By Tree view to find and select the database.
    3. Open Agent Option.
    4. Confirm that Automatic Selection is selected.
    5. Under Subset, select Torn Page Repair – Offline.
    6. Under Recovery Completion State, select Leave database operational.
    7. (Optional) You may select a Database Consistency Check after the restore.
    8. Click OK.
    9. On the Destination tab, select Restore to Original Location, if it is not selected.
    10. Submit the Restore job.
  6. Change to Simple Recovery Model, if you changed the Recovery Model in step 1.
  7. Resume use of the database.