Restoring Microsoft SQL Server Databases › Restore to Alternate Disk Locations by Individual Session › Restore by Tree Using a Separate Job for Each Session

Restore by Tree Using a Separate Job for Each Session

If you are using the Restore by Tree method, you must submit each session as a separate restore job. You may want to submit each job on hold and make each job ready individually as the previous one finishes.

To restore sessions as separate jobs using the Restore by Tree method

  1. On the Restore Manager Source tab, select Restore by Tree from the drop-down list.
  2. In the navigation tree, expand the computer from which the database you want to restore was backed up. Click the yellow database icon to expand the database instance containing the database you want to restore and select the database.
  3. Click Version History. The Version History dialog appears. Scroll to the right to find the columns labeled Method and Backup Time.

    Note: Entries appear in reverse chronological order; more recent backups appear higher on the list.

  4. Select the most recent backup with the method Database and click Select.
  5. Right-click the selected database session and select Agent Options from the pop-up menu. The Agent Restore Options dialog opens.
  6. Edit the file names or paths as appropriate, and select the Leave database nonoperational, but able to restore additional transaction logs option under Recovery Completion State.
  7. Click OK to close the Agent Restore Options dialog and submit this restore job. For instructions about restoring by tree, see Restore Databases Using Restore by Tree Method.
  8. Click Version History again and select the next backup session.
  9. Open the Agent Restore Options dialog. Clear the Automatic Selection option.
  10. If this is not the last session to be restored, select the Leave database nonoperational, but able to restore additional transaction logs option under Recovery Completion State.

    If this is the last session to be restored, confirm that the Leave database operational. No additional transaction logs can be restored option is selected under Recovery Completion State.

  11. Click OK to close the Agent Restore Options dialog.
  12. Submit the restore job. For instructions about restoring by tree, see Restore Databases Using Restore by Tree Method.
  13. Repeat these steps from the point at which you close the Agent Restore Options dialog and submit the restore job until all backup sessions have been submitted for a restore.

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

Microsoft SQL Server 2005 and 2008 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.

To perform an offline torn page restore using Microsoft SQL Server 2005

  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.

Perform an Online Torn Page Restore Using Microsoft SQL Server 2005 or 2008 Enterprise, Data Center or Developer Editions

Microsoft SQL Server 2005 and 2008 have the ability to detect when the data in the database has been damaged, and isolate the damage at the data page level. At any point in time, the current list of known damaged pages can be found in the [suspect_pages] table of system database [msdb]. 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 service quickly.

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

To perform an online torn page restore using Microsoft SQL Server 2005 or 2008 Enterprise, Data Center, or Developer Editions

  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. Perform an Online 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 – Online.
    6. Under Recovery Completion State, select Leave database operational.
    7. (Optional) You may select a Database Consistency Check before the restore, to identify any other damaged or corrupted pages. (Note that this is the only restore type for which a DBCC is permitted before a Restore, because DBCC requires the database to be online.)
    8. (Optional) You may select a Database Consistency Check after the restore.
    9. Click OK.
    10. On the Destination tab, select Restore to Original Location, if it is not selected.
    11. Start the Restore.
  4. Attempt to query the table that contained the damaged page.
  5. Perform a Transaction Log backup with the default options.
  6. Perform a restore of this final Transaction Log backup without Automatic Selection, and with the Recovery Completion State set to Leave Database operational.
  7. Change to Simple Recovery Model, if you changed the Recovery Model in step 1.
  8. Resume use of the database.

Copyright © 2010 CA. All rights reserved. Email CA about this topic