Home » System Center Data Protection Manager » DPM 2010 – SQL Server End-user recovery


Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Azure infrastructure and system management. I am a senior engineer in the FastTrack for Azure team, part of Azure Engineering, at Microsoft.  Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.

Anders Bengtsson

MVP awarded 2007,2008,2009,2010

My Books
Service Manager Unleashed
Service Manager Unleashed
Orchestrator Unleashed
Orchestrator 2012 Unleashed
Inside the Microsoft Operations Management Suite

DPM 2010 – SQL Server End-user recovery

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.


Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.