Data Protection Manager (DPM) 2010 (currently in beta) allows backup administrators to authorize SQL Server database owners to recover their databases without intervention from the backup administrator.

To do this, the DPM administrator must create and manage DPM roles. A DPM role allows backup administrators to control what an end-user can recover and which instances of SQL Server they can recover to. A DPM role for SQL Server End-User Recovery (SQL Server EUR) includes the following:

• Users: A security group that represents a set of end users.
• Objects: SQL Server databases that can be recovered and instances of SQL Server that have been identified for an alternate instance recovery.
• Recovery permissions for alternate instance recovery, which a DPM administrator uses for managing SQL Server EUR functionalities used by end-users.

 

Source: DPM 2010 beta documentation

The first thing you need to do is to install the SQL Server end-user recovery client on the protected machine, in this example hq-opsmgr28. The EUR client application enables end-users to perform recoveries of databases.

After that you need to create a role, that is done, in the current beta, with DPM Command shell. The following example creates a role named “Operations Manager SQL”.

$Role = New-DPMRole -name “OpsMgrSQL” -description “Operations Manager SQL” -DPMServerName HQ-DPM55
Add-DPMSecurityGroup -DPMRole $Role -SecurityGroups “Hq\OpsMgrSQL”
$DatabaseForEndUserRecovery = $null
$ListOfPGs = Get-ProtectionGroup -DPMServerName HQ-DPM55
$ListOfPGs | ForEach-Object {if ($_.FriendlyName -eq “SQL Protection Group”) {$PG = $_; break }}
$DatasourceInPG = Get-Datasource $PG
$DatasourceInPG | ForEach-Object {if ($_.LogicalPath -eq “HQ-OPSMGR28\OperationsManager”) {$DatabasesForEndUserRecovery +=,$_}}
Add-DPMRecoveryItem -DPMRole $Role -type SQLDatabase -datasource $DatabasesForEndUserRecovery
Add-DPMRecoveryItem -DPMRole $Role -type SQLInstance -sqlinstances “HQ-OPSMGR28”
$RecoveryTargetInstance = New-DPMRecoveryTarget -Type SQLINSTANCE -RecoveryTarget “HQ-OPSMGR28\Recovery” -RecoveredFilesPath C:\TEMP
Add-DPMRecoveryTarget $Role $RecoveryTargetInstance
Set-DPMRole -DPMRole $Role

This script will create a new role named OpsMgrSQL. It will add the security group HQ\OpsMgrSQL to this role. Get the list of protection groups and all data sources in SQL Protection Group, and then add the OperationsManager database to the list of databases we want to allow the role to recover . Then add the list to the role as recovery item. Add the required instance whose database we want to recover, and add the target instance and recovery target. Finally we save the role.

Members of the HQ\OpsMgrSQL group can now connect to the DPM server HQ-DPM55 and restore the OperationsManager database (from the hq-opsmgr28 server). The OperationsManager database is protected by the SQL Protection Group and can be restored to the HQ-OPSMGR28\Recovery SQL instance only.

From the end-user recovery wizard, in the protected server, the end-user can now recover the database

 

 

 

 

The OperationsManager database is restored and back online again. Performed by the SQL administrators without any delay or wait for backup administrators.