How to Recover Data for SQL Servers

Applies To: System Center Data Protection Manager 2010

Data Protection Manager (DPM) supports recovery of SQL Server databases at the instance level through the DPM Recovery Wizard. You can use the following procedure to recover data for computers running SQL Server.

Important

If you migrated from SQL Server 2000 to SQL Server 2005, if the SQL Server 2000 data was being protected by DPM, and if you restored data from the SQL Server 2000 recovery point to the SQL Server 2005 original location, you must run a consistency check immedately after the recovery is completed.

To recover protected data for computers running SQL Server

  1. In DPM Administrator Console, click Recovery on the navigation bar.

  2. Select a SQL instance to view recoverable SQL Server databases. If the database is not part of a protection group, the calendar is disabled, and the database data is not available for recovery, DPM displays the message No recovery points are available for the items below because they do not belong to any protection group.

  3. Browse or search for the data you want to recover, and then, in the results pane, select the data.

  4. Available recovery points are indicated in bold on the calendar in the recovery points section. Select the date for the recovery points you want to recover, and then select the time in the Recovery time drop-down menu. If you intend to rename and recover the database, do not select Latest for the recovery time.

  5. Click to select the recoverable item you want to recover.

  6. In the Actions pane, click Recover. DPM starts the Recovery Wizard.

  7. Review your recovery selection, and click Next.

  8. Specify the type of recovery you would like to perform:

    1. Recovery to original instance of SQL Server. The current database files will be overwritten during recovery.

    2. Rename and recover the database. This option allows you to keep both the existing database and the recovered database in the original instance of SQL Server. You can specify a new name for the recovered database.

      Note

      You can rename and recover the database only for SQL Server 2005 databases, not for SQL Server 2000 databases.

    3. Copy to a network folder. Click Browse, and select the network folder from the list.

    4. Copy to tape. This option copies the selected backup of the database to a tape in a DPM library so that you have a copy of the database backup. Click Next, and specify library and tape options. You can also choose to encrypt or compress data.

    Click Next after you specified one of the preceding options.

  9. Specify the database state:

    1. Recover database. This option performs full recovery and leaves the database ready to use.

    2. Recover and leave database in restoring state. This option recovers the database but leaves it non-operational.

  10. If logs are available for the selected database, you can copy SQL transaction logs between the selected database and the latest database available for recovery. This option is disabled if there are no logs available for the selected database. To copy SQL transaction logs between the selected version of the database and the latest version available for recovery, in the Database in restoring state option pane, click Copy SQL transaction logs between the selected and latest available recovery.

  11. Specify recovery options for network bandwidth usage throttling and e-mail notifications, and click Next.

  12. Review your recovery settings, and click Recover.

    Note

    Any synchronization job for the selected recovery item is canceled while the recovery is in progress.

To set recovery options for a SQL Server database by using DPM Management Shell

  • Use the following syntax to set recovery options for a SQL server:

    New-RecoveryOption [-TargetServer] <String> [-RecoveryLocation] <RecoveryLocation> [-DPMLibrary <Library> ] [-RecoverToReplicaFromTape] [-SANRecovery] [-RestoreSecurity] -SQL -RecoveryType <RecoveryType> [-RollForwardRecovery] [-TargetLocation <String> ] [-AlternateDatabaseDetails <AlternateDatabaseDetailsType> ] [-LeaveDBInRestoringState] [-CopyLogFiles] [-LogFileCopyLocation <String> ] [-Verbose] [-Debug] [-ErrorAction <ActionPreference> ] [-ErrorVariable <String> ] [-OutVariable <String> ] [-OutBuffer <Int32> ]

    For more information, type "Get-Help New-RecoveryOption -detailed" in DPM Management Shell.

    For technical information, type "Get-Help New-RecoveryOption -full" in DPM Management Shell.

To recover protected data for a SQL Server database by using DPM Management Shell

  • Use the following syntax to recover data for a SQL server:

    Recover-RecoverableItem [-RecoverableItem] <RecoverableObject[]> [-RecoveryOption] <RecoveryOptions> [-RecoveryPointLocation <RecoverySourceLocation[]> ] [-JobStateChangedEventHandler <JobStateChangedEventHandler> ] [-RecoveryNotification <Nullable`1> ] [-Verbose] [-Debug] [-ErrorAction <ActionPreference> ] [-ErrorVariable <String> ] [-OutVariable <String> ] [-OutBuffer <Int32> ]

    For more information, type "Get-Help Recover-RecoverableItem -detailed" in DPM Management Shell.

    For technical information, type "Get-Help Recover-RecoverableItem -full" in DPM Management Shell.

See Also

Tasks

How to Enable Computer-Level Network Bandwidth Usage Throttling

Concepts

Recovering Data