In my sandbox I test a lot of solutions, management packs, integration packs and ideas. They don’t always work out the way expected :) The result is that I often need to restore a database from backup. I use Data Protection Manager to protect my databases, Service Manager to order the restore and Orchestrator as the “doer”. In this blog post I want to share a example how to make restore of database a bit easier.

In the Service Manager self-service portal I have a request offering named Restore Database.

  • Restore to Original Location. If this checkbox is enabled the database will be restored to original instance. In most cases I restore to a network folder. Network Folder is used in this integration pack in the same way that it is used in the Data Protection Manager user interface. Choosing Network Folder recovers to a local path on a server that have the DPM agent installed. I have configured my runbook (also included in this blog post) to always recover to C:\RESTORE on the target machine
  • Target server. If I select to restore to a network folder, default, I input a server name. For example if I want to restore the Orchestrator database to my Orchestrator database server I input SCO12SP1-SQL01 in the Target Server text box. The database backup will then be restored to C:\RESTORE in the SCO12SP1-SQL01 server.
  • Recovery Point to Restore. In this query based list I can select which DPM recovery point to restore. I have a runbook (also included in this blog post) that create CI of each recovery point.

20130102_DPM_SelfService05

Service Manager invokes the “1.2 Restore” runbook in Orchestrator. The runbook is divided into two tracks depending if restoring to a network folder or to original location. Both Data Source ID and Recovery Source ID, used to recover the SQL database, is stored on the Backup CI in Service Manager so we dont need to get them from DPM within the runbook. In general the runbook restore the database and updates the service request.

20130102_DPM_SelfService02Runbook “1.1 Create Backup CIs” is the second runbook in this example. It is used to create backup CI objects in Service Manager. The backup class is a custom class that I have created with the Service Manager authoring tool. The runbook runs every hour and creates new/updates/deletes CIs of the backup class.

  • Every hour.  Invokes the runbook every hour
  • Get Existing Backup. Gets all objects of the backup class in Service Manager. If there are any objects the “Set Verified to FALSE” activity change the verified property of all the backup CIs to FALSE.
  • Junction. Used to merge possible multiple threads to one
  • Get Data Source for System Center DBs. In my DPM server I have a protection group named “System Center Databases”, this activity gets all data sources for that protection group
  • Get All Recovery Point. This activity gets all recovery points for the data sources returned by the “Get Data Sources for System Center DBs” activity
  • Check if Backup Exist. This activity checks in Service Manager if there is a backup CI, with Active status, for the current BackupID. BackupID is a property of the backup class that I use to give all recovery points a unique ID, the backup ID contains of <Protection Group Name>.<Production Server Name>.<Recovery Time Point in Time>.<Data source Name>
  • If a backup CI object already exist the runbook change the verified property of the backup CI object to TRUE
  • If no backup CI object exist a new backup CI is created and a relationship to the server is created
  • Junction. Used to merge possible multiple threads to one
  • Get Non Verified Backups. This activity gets all backup CIs that has not been verified (verified property equals FALSE) and deletes them with the “Delete Backup” activity

 

20130102_DPM_SelfService01

 

Backup CIs listed in the Service Manager Console

20130102_DPM_SelfService03

Backup CI

20130102_DPM_SelfService04Relationship between windows server and backup CI

20130102_DPM_SelfService06

 

When the runbook is done the service request is updated with some information, which can be read from the Service Manager self-service portal. As you can see the database has been restored to C:\RESTORE on the SCO12SP1SQL-01 server. A very Quick and easy way to roll back a database.

20130102_DPM_SelfService07

 

You can download my example files here, 20130103_DPM. Note that this is provided “AS-IS” with no warranties at all. This is not a production ready management pack or solution for your production environment, just a idea and an example.