Export (0) Print
Expand All

Backup methods for the DPM database

Updated: December 31, 2014

Applies To: System Center 2012 R2 Data Protection Manager, System Center 2012 SP1 - Data Protection Manager

As part of your DPM backup strategy you’ll need to back up the DPM database - DPMDB. This database contains the DPM configuration and in case of disaster you can rebuild most of the functionality of a DPM server with a recent backup of the database. In detail - if you can restore the database, tape- based backups would be accessible and all protection group settings and backup schedules would be maintained. If the DPM storage pool disks were not affected by the outage then the disk-based backups would also be usable after a rebuild. You can backup the database with a few different methods.

 

Database backup method Advantages Disadvantages

Back up to Azure

Easily configured and monitored in DPM.

Multiple locations of the backup database files.

Cloud storage provides a robust solution for disaster recovery.

Very secure storage for the database.

Supports 120 online recovery points.

Only available on DPM 2012 SP1 or later.

Requires Azure account and additional DPM configuration. Incurs some cost for Azure storage.

- Requires an alternate Windows Server 2012 based system with the Azure agent to gain access to DPM backups stored in the Azure backup vault. This can’t be another DPM server.

Not an option if the database is hosted locally and you want to enable secondary protection. A workaround would be to use a remote SQL Server to host the database.

Some extra preparation and recovery time is incurred.

Some Windows PowerShell commands are required so PowerShell knowledge can be helpful.

Back up to the DPM storage pool

Simple to configure and monitor.

The backup is kept on the DPM storage pool disks and is easy to access locally.

DPM scheduled backups support 512 express full backups. If you back up hourly you’ll have 21 days of full protection.

Not a good option for disaster recovery. It’s online and recovery might not work as expected if the DPM server or storage pool disk fails.

Not an option if the database is hosted locally and you want to enable secondary protection. A workaround would be to use a remote SQL Server to host the database.

Some preparation and special steps are required to gain access to the recovery points if the DPM service or console isn’t running or working.

Back up with the DpmBackup tool

Independent of DPM.

Easy to run for instant on-demand backups

Can be scheduled as often as you want using Windows Task Scheduler.

The backup is kept on a local disk which is easily accessible.

Not a good option for disaster recovery unless the backups are copied to a remote location.

By default the Dpmdb.bak file is overwritten each time dpmbackup.exe is run, so there will be no previous versions unless you rename it afterwards.

Requires local storage.

Back up to a secondary DPM server

Easily configured and monitored in DPM.

DPM scheduled backups support 512 express full backups. If done hourly, this provides 21 days of short term protection. If done every 30 minutes it provides 10 days protection.

The backup is kept on the secondary DPM server storage pool disks which are locally accessible.

Provides a good disaster recovery solution if secondary DPM server is offsite.

Additional DPM server and storage are required. Both DPM servers must to be running the same DPM version and update rollups.

Back up to tape

Easily configured and monitored in DPM.

DPM scheduled tape backups support retention up to 99 years.

Tape backup can be taken offsite for disaster recovery.

Tape backup can be restored from any other DPM server that has a tape drive/library attached that uses the same tape media type.

Tape can be encrypted for secure storage.

Not an option if the database is hosted locally and you want to enable secondary protection. A workaround would be to use a remote SQL Server to host the database.

Only one tape backup per day can be scheduled.

You need a working DPM server with a tape library to be able to read a DPM backup tape that contains the copy of the database you want to restore.

Some preparation and special steps are required to gain access to the tape based recovery points.

Back up with native SQL Server backup to a local disk

Built-in to SQL Server.

The backup is kept on a local disk which is easily accessible.

It can be scheduled to run as often as you like.

Totally independent of DPM.

You can schedule a backup file cleanup.

Not a good option for disaster recovery unless the backups are copied to a remote location.

Requires local storage for backups which may limit retention and frequency.

Back up with native SQL Server backup to a share protected by DPM

Easily monitored in DPM.

Multiple locations of the backup database files.

Easily accessible from any Windows machine on the network.

Potentially the fastest recovery method.

Only supports 64 recovery points.

Not a good option for site disaster recovery. DPM server or DPM storage pool disk failure may hinder recovery efforts.

Not an option if the DPM DB is hosted locally and you want to enable secondary protection. A workaround would be to use a remote SQL Server to host the DPMDB.

Some extra preparation is needed to get it configured and tested.

Some extra preparation and recovery time is needed should the DPM server itself be down but DPM storage pool disks are fine.

  • If you back up using a DPM protection group, recommend that you use a unique protection group for the database.

  • If you’re backing up to tape, make at least two copies of the backup tapes, and store each of the backup tapes in a different remote location

  • If the DPM SQL Server instance isn’t running on the DPM server, install the DPM protection agent on the SQL Server computer before you can protect the DPM databases on that server.

  • Note that for restore purposes the DPM installation you want to restore with the DPM database must match the version of the DPM database itself.  For example, if the database you want to recover is from a DPM 2012 R2 with Update Rollup 4 installation, then the DPM server must be running the same version with Update Rollup 4. This means that you might need to uninstall and reinstall DPM with a compatible version before you restore the database.  To see the database version you might need to mount it manually to a temporary database name then run a SQL query against the database to check the last rollup installed based on the major and minor version. To run the query open SQL Management Studio and connect to the SQL instance running the DPM database. Select it and start a new query. Paste the following into the query pane and run it: Select distinct MajorVersionNumber,MinorVersionNumber ,BuildNumber, FileName FROM dbo.tbl_AM_AgentPatch order byMajorVersionNumber,MinorVersionNumber,BuildNumberIf nothing is returned, or if the DPM server was upgraded from previous versions but no new update rollup was installed since then there won’t be an entry for the major, minor for a base installation of DPM. To check the DPM versions associated with update rollups see List of Build Numbers for System Center Data Protection Manager (DPM).

Back up to Azure

You can back up the DPM database to Azure as follows:

Before you start

  • To recover from Azure d you’ll need to know the DPM replica volume mount point path so that you know which recovery point contains the DPM backup. You should do this after the initial replication and before you You can use this script to do that. Replace dpmsqlservername% with the name of the SQL Server hosting the database.

    Select ag.NetbiosName as ServerName,ds.DataSourceName,vol.MountPointPath
    from tbl_IM_DataSource as ds
    join tbl_PRM_LogicalReplica as lr on ds.DataSourceId=lr.DataSourceId
    join tbl_AM_Server as ag on ds.ServerId=ag.ServerId
    join tbl_SPM_Volume as vol on lr.PhysicalReplicaId=vol.VolumeSetID
    and vol.Usage =1 
    and lr.Validity in (1,2) 
    where ds.datasourcename like '%dpmdb%'
    and servername like '%dpmsqlservername%' --netbios name of server hosting DPMDB
    
  • Make sure you have the passcode that was specified when the Azure Recovery Services Agent was installed and the DPM server was registered in the Azure Backup vault. You’ll need this passcode to restore the backup.

Configure the backup

  1. Create an Azure Backup vault.

  2. Download the Azure Backup Agent installation file and vault credentials.

  3. Install the Agent on the DPM server and use the downloaded credentials to register the server in the vault.

  4. Configure a protection group that contains the DPM database, and on the Select Data Protection Method page of the Create New Protection Group wizard, select to back up to Azure.

Read Set up DPM backup to Azure for more information.

Recover the database from Azure

  1. The vault credentials you originally used to register the DPM server in the vault expire after a couple of days. Since the recovery server will need the same credentials file as the DPM server, generate a new credentials file in the Azure portal. Download it to a location accessible by the DPM server registered in the vault and by the Windows server.

  2. Rerun the Azure Recovery Services Agent install on the DPM server and select the new credentials file.

  3. On a server running Windows 2008 R2 or 2012 that has internet access, run the Agent installation and select the new vault credentials file to register the server. The server mustn’t have DPM installed.

  4. After registration open a Windows PowerShell command window using administrative privileges. The PowerShell commands below detail a single recovery from a backup vault that has backups from two DPM servers. It shows how to restore the latest database backup for LC2-DPMLIB2 from the backup vault.

    Windows PowerShell
    Copyright (C) 2012 Microsoft Corporation. All rights reserved.
    #get a list of servers available to recover backups for.
    
    PS C:\Windows\system32> $Server=Get-OBAlternateBackupServer 
    PS C:\Windows\system32> $server    #display the list of servers
    
    ServerName : lc2-dpmlib1.Contoso.com
    ServerName : lc2-dpmlib2.Contoso.com   # we will restore this DPM servers backup.
     
    # The [1] is a zero based index from the list of servers returned above.
     
    PS C:\Windows\system32> $source=Get-OBRecoverableSource -Server $server[1] 
    PS C:\Windows\system32> $source
     
    # Note the datasource path from the SQL Script you ran in preparation step - we want to list recovery points for that data source.  In this case vol_850b95be-b942-4351-83bd-0a1815a936b2.
     
    FriendlyName       : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    RecoverySourceName : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    ServerName         : lc2-dpmlib2.Contoso.com
     
    FriendlyName       : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_79d00c30-4329-4542-b874-ada91b78f90b\
    RecoverySourceName : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_79d00c30-4329-4542-b874-ada91b78f90b\
    ServerName         : lc2-dpmlib2.Contoso.com
     
     
    # list recovery points for the first datasource [0] highlighted above.
     
    PS C:\Windows\system32> $item=Get-OBRecoverableItem -Source $source[0]
    PS C:\Windows\system32> $item
     
    # Note the date / time for the three PointInTime backups listed below.
     
    IsDir                : False
    ItemNameFriendly     : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    ItemNameGuid         : \\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\
    LocalMountPoint      : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    MountPointName       : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    Name                 : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    PointInTime          : 6/18/2014 1:00:13 AM
    ServerName           : lc2-dpmlib2.Contoso.com
    ItemSize             :
    ItemLastModifiedTime :
     
    IsDir                : False
    ItemNameFriendly     : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    ItemNameGuid         : \\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\
    LocalMountPoint      : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    MountPointName       : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    Name                 : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    PointInTime          : 6/17/2014 1:00:18 AM
    ServerName           : lc2-dpmlib2.Contoso.com
    ItemSize             :
    ItemLastModifiedTime :
     
    IsDir                : False
    ItemNameFriendly     : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    ItemNameGuid         : \\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\
    LocalMountPoint      : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    MountPointName       : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    Name                 : c:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\
    PointInTime          : 6/16/2014 1:01:16 AM
    ServerName           : lc2-dpmlib2.Contoso.com
    ItemSize             :
    ItemLastModifiedTime :
     
    # we're creating $option variable for the recovery locaton c:\temp - adjust accordingly.
     
    PS C:\Windows\system32> $option = new-OBRecoveryOption -Destinationpath c:\temp -Overwritetype CreateCopy
     
    # Here you need the same DPMPassPhrase used on the DPM Server that created the backup.
     
    PS C:\Windows\system32> $key = ConvertTo-Securestring "DPMPassPhrase" -Asplaintext -force
     
    # Start the recovery for the first backup time 6/18/2014 1:00:13 AM  from above which is $item[0].
     
    PS C:\Windows\system32> start-OBRecovery -recoverableItem $item[0] -EncryptionPassPhrase $key -recoveryOption $option
    Preparing storage...
    Estimating size of backup items...
    Estimating size of backup items...
    Estimating size of backup items...
    Estimating size of backup items...
    Estimating size of backup items...
    Estimating size of backup items...
    Estimating size of backup items...
    Estimating size of backup items...
    Estimating size of backup items...
    Transferring data...
    Transferring data...
    Transferring data...
    Transferring data...
    Transferring data...
    Transferring data...
    Transferring data...
    Transferring data...
    Transferring data...
    Transferring data...
    Transferring data...
    Data transfer completed
    Job completed.
    The recovery operation completed successfully.
    PS C:\Windows\system32>
    
    

    After a successful recovery the DPM database files will be in the location specified with the $option variable above.

Back up to the DPM storage pool

With this method you back up the DPM database as you would any other protected data source.

Before you start

  • In order to recover you’ll need to know which volume contains the backed up database. To do this check the DPM replica volume mount point path or volume GUID after initial replication but before you need to restore. Use this SQL Server script to retrieve the information and store it in a safe place that will be accessible if disaster occurs. Replace %dpmsqlservername% with the name of the SQL Server hosting the database.

    Select ag.NetbiosName as 
    ServerName,ds.DataSourceName,vol.MountPointPath,vol.GuidName 
    from tbl_IM_DataSource as ds
    join tbl_PRM_LogicalReplica as lr on ds.DataSourceId=lr.DataSourceId
    join tbl_AM_Server as ag on ds.ServerId=ag.ServerId
    join tbl_SPM_Volume as vol on lr.PhysicalReplicaId=vol.VolumeSetID
    and vol.Usage =1 -- Replica=1, DiffArea=2
    and lr.Validity in (1,2) 
    where ds.datasourcename like '%dpmdb%'
    and servername like '%dpmsqlservername%' --netbios name of server hosting DPMDB
    
    
  • Obtain a copy of PsExec.exe and place it on the DPM server.

Configure the backup

  1. In DPM Administrator Console, click Protection on the navigation bar and click Create protection group in the Actions pane.

  2. On the Select Protection Group Type page, select Servers.

  3. On the Select group members  page, select the DPM database. If you’re running SQL Server remotely select the remote SQL Server installed and select DPM database. If SQL Server is running on the DPM server expand the DPM server item and select DPMDB.

  4. On the Select Data Protection Method  page, select I want short-term protection using disk. Specify the short-term protection policy options. We recommend a retention range of two weeks for DPM databases.

Recover the database

Assuming the DPM server itself is still operational and the storage pool is intact but the DPM service or console has problems do the follows to copy the database from the replica volume or a shadow copy.

  1. Decide the time from which you want to recover the database:

    • If you want to copy the database from the last backup taken directly from the DPM replica volume, use mountvol.exe to assign a drive letter to the replica volume using the GUID from the SQL script output. For example: C:\Mountvol X: \\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\

    • If you want to copy the database from a previous recovery point (shadow copy) then you need to list all the shadow copies for the replica using the volume GUID from the SQL script output. This command lists shadow copies for that volume: C:\>Vssadmin list shadows /for=\\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\. Note the creation time and the shadow copy ID  you want to recover from. Here’s an example:

      C:\Windows\system32>vssadmin list shadows /for=\\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\
      vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool
      (C) Copyright 2001-2013 Microsoft Corp.
      Contents of shadow copy set ID: {7c67f31b-9b5b-45fc-8c9c-3688cce6bc87}
         Contained 1 shadow copies at creation time: 7/1/2014 8:00:03 PM
            Shadow Copy ID: {9f521455-dd96-4a80-8ad0-b5b1892c2f31}
               Original Volume: (C:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\)\\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\
               Shadow Copy Volume: \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy162
               Originating Machine: lc2-dpmlib2.Contoso.com
               Service Machine: lc2-dpmlib2.Contoso.com
               Provider: 'Microsoft Software Shadow Copy provider 1.0'
               Type: DataVolumeRollback
               Attributes: Persistent, No auto release, No writers, Differential
       
      Contents of shadow copy set ID: {c23c0987-4ebe-462f-9bd4-c90ffbefc725}
         Contained 1 shadow copies at creation time: 7/2/2014 8:00:02 PM
            Shadow Copy ID: {ad959229-4f9f-43ce-8c84-014fdbf81a08}
               Original Volume: (C:\Program Files\Microsoft DPM\DPM\Volumes\Replica\SqlServerWriter\vol_850b95be-b942-4351-83bd-0a1815a936b2\)\\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\
               Shadow Copy Volume: \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy164
               Originating Machine: lc2-dpmlib2.Contoso.com
               Service Machine: lc2-dpmlib2.Contoso.com
               Provider: 'Microsoft Software Shadow Copy provider 1.0'
               Type: DataVolumeRollback
               Attributes: Persistent, No auto release, No writers, Differential
      
      
  2. Now use diskshadow.exe to mount the shadow copy to an unused drive letter X: using the shadow copy ID so you can copy the database files. Here’s an example:

    C:\>diskshadow.exe
    Microsoft DiskShadow version 1.0
    Copyright (C) 2013 Microsoft Corporation
    On computer:  LC2-DPMLIB2,  7/3/2014 4:31:42 PM
    DISKSHADOW> expose {ad959229-4f9f-43ce-8c84-014fdbf81a08} X:
    
    
    The shadow copy was successfully exposed as X:\.

    DISKSHADOW> exit
    
    
    Open an administrative command prompt and run psexec.exe -s cmd.exe to start a command prompt in system context so you have permissions to navigate the replica volume (X:) to copy out the files.

    C:\>psexec.exe -s cmd
    PsExec v1.96 - Execute processes remotely
    Copyright (C) 2001-2009 Mark Russinovich
    Sysinternals - www.sysinternals.com
     
    Microsoft Windows [Version 6.3.9600]
    (c) 2013 Microsoft Corporation. All rights reserved.
     
          C:\Windows\system32>
    
    
    Now CD to the X: drive and navigate down to the location of DPM SQL database files and copy them to a location that is easy to restore from.

    C:\Windows\system32>X:
    X:\>dir
     Volume in drive X is DPM-vol_850b95be-b942-4351-
     Volume Serial Number is 6E39-5066
     Directory of X:\
     
    07/01/2014  08:10 PM    <DIR>          26ee79bf-f37d-49ac-970c-cfb1d016b39c
    06/18/2014  08:00 PM                30 {26EE79BF-F37D-49AC-970C-CFB1D016B39C}checkpoint
                   1 File(s)             30 bytes
                   1 Dir(s)   8,654,036,992 bytes free
     
    X:\>cd 26ee79bf-f37d-49ac-970c-cfb1d016b39c
    X:\26ee79bf-f37d-49ac-970c-cfb1d016b39c>
    ...
    ..
    .
    X:\26ee79bf-f37d-49ac-970c-cfb1d016b39c\Full\C-Vol\Program Files\Microsoft DPM\DPM\DPMDB>dir
     Volume in drive E is DPM-vol_850b95be-b942-4351-
     Volume Serial Number is 6E39-5066
     
     Directory of X:\26ee79bf-f37d-49ac-970c-cfb1d016b39c\Full\C-Vol\Program Files\Microsoft DPM\DPM\DPMDB
    02/10/2014  11:28 AM    <DIR>          .
    02/10/2014  11:28 AM    <DIR>          ..
    06/24/2014  06:58 PM     7,171,211,264 MSDPM2012$DPMDB.mdf
    06/24/2014  06:58 PM    27,038,842,880 MSDPM2012$DPMDB_log.ldf
                   2 File(s) 34,210,054,144 bytes
                   2 Dir(s)   8,654,036,992 bytes free
    X:\26ee79bf-f37d-49ac-970c-cfb1d016b39c\Full\C-Vol\Program Files\Microsoft DPM\DPM\DPMDB>copy *.* c:\temp
    MSDPM2012$DPMDB.mdf
    MSDPM2012$DPMDB_log.ldf
     2 file(s) copied.
    
    
    After the copy is complete, exit the psexec cmd window, then run diskshadow.exe and unexpose the x: volume.

    C:\>Diskshadow.exe
    DISKSHADOW> unexpose X:
    Shadow copy ID {ad959229-4f9f-43ce-8c84-014fdbf81a08} is no longer exposed.%DPMDB
    
    
    You can now restore the database files by using SQL Management Studio or by running DPMSYNC –RESTOREDB. See Recover with the DPMSync tool.

Back up with the DpmBackup tool

DpmBackup.exe is a command-line tool included in DPM. It backups up the DPM databases (DPMDB and ReportServer). It also creates shadow copies for each replica volume and mounts them to the Volumes\ShadowCopy path from where tapes can access them for backup. You use it to automate the creation of backups for the DPM databases. It can also create and mount backup shadow copies for replica volumes stored on the DPM server.

Before you start

  • On a remote SQL server, the Dpmbackup.exe utility is located in the SQLPREP folder under C:\Program Files\Microsoft Data Protection Manager\<DPM_Version>.

  • You can also download the latest version of the backup tool from the download center.

Configure the backup

  1. Post-DPM 2012 SP1 DPMBackup.exe switches changed and now support sharing a SQL Server instance between multiple DPM servers. If you have a mixture of DPM servers, we recommend you run the tool with the full syntax in all cases to be sure the backups work. We also recommend that you use a common local target location so that it’s consistent across all DPM servers. Recommended switches:

    • Dpmbackup.exe -db -instancename DPMINSTANCE -dpmdbname DPMDB_NAME -targetlocation C:\DPMDBBACKUPS

    • If the SQL Server is using the default instance (MSSQLSERVER) then the switches are:Dpmbackup.exe -db -instancename (Local) -dpmdbname DPMDB_NAME -targetlocation C:\DPMDBBACKUPS

  2. Copy the backed up database file to a secure location as follows:

    • If the backed up database is located on the DPM server at the command line navigate to <system drive>:\Program Files\Microsoft DPM\DPM\Volumes\ShadowCopy\Database Backups and copy the database to a secure location that you can access when you are ready to restore your DPM database.

    • If the database is on a remote computer navigate to <system drive>:\DPMBackup\dpmserver and copy the database to a secure location.

  3. If you want to automate the tool do the following:

    1. Open Notepad. Copy and paste the script below, and save it to a file called backupdb.cmd on the server hosting the DPM database.

    2. Change the backupdir variable in the script to a local location to store the DPM database backups. Be sure there is adequate space for the frequency and duration of the backups based on the database size. You can enable NTFS compression on the target folder to help save space.

      Echo off
      Set backupdir=c:\DPMDBBACKUPS 
      cd %backupdir%
      REM Build environment variables for current date, time to be used in backup command strings to create unique DPMDB.BAK names.
      for /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do set dt=%%i-%%j-%%k-%%l
      for /f "Tokens=1-2 Delims=: " %%i in ('time /t') do set tm=%%i-%%j
      set tm=%tm%
      set dtt=%dt%-%tm%
      REM Build environment variables for current day of the week.
      REM
      for /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do set DAYOFWEEK=%%i
      Dpmbackup.exe -db -instancename MSDPM2012 -dpmdbname DPMDB -targetlocation %backupdir% >DPMBACKUP-STATUS.TXT
      if %errorlevel% NEQ 0 goto exit
      Ren dpmdb.bak %DTT%-DPMDB.BAK
      echo.>>DPMBACKUP-STATUS.TXT
      echo last backup was on %date% at %time% >>DPMBACKUP-STATUS.TXT
      REM Delete oldest backups 
      IF "%DAYOFWEEK%" == "Sun" goto Sun
      IF "%DAYOFWEEK%" == "Mon" goto Mon
      IF "%DAYOFWEEK%" == "Tue" goto Tue
      IF "%DAYOFWEEK%" == "Wed" goto Wed
      IF "%DAYOFWEEK%" == "Thu" goto Thu
      IF "%DAYOFWEEK%" == "Fri" goto Fri
      IF "%DAYOFWEEK%" == "Sat" goto Sat
      :Sun
      Del Mon*.bak
      exit
      :Mon
      Del Tue*.bak
      exit
      :Tue
      Del Wed*.bak
      exit
      :Wed
      Del Thu*.bak
      exit
      :Thu
      Del Fri*.bak
      exit
      :Fri
      Del Sat*.bak
      exit
      :Sat
      Del Sun*.bak
      exit
      
      
    3. Using Windows Task Scheduler, schedule backupdb.cmd to run as often as you like.

    4. Check that the backup files are created after the scheduled time. The batch file will keep as many backups as you schedule to run each day because the dpmdb.bak file is renamed using day-mm-dd-yyyy-hh-mm-dpmdb.bak. The script deletes the oldest backup files automatically after a week. After midnight, it deletes the oldest day’s backup (i.e. the first backup on Sunday will delete last Monday’s backups).

Recover the database

Recover with the DPMSync tool.

Back up to a secondary DPM server

Configure the backup

  1. On the secondary DPM server push the protection agent to the server on which the DPM database is installed – either on the primary DPM server or on a remote SQL Server. After installation the server will appear in Unprotected server with protection agents and should show status OK when refreshed.



  2. Create a new protection group. In Select group member choose the server hosting the DPM database. In All SQL Servers select the database you want to protect.

  3. In the Select Data Protection Method page select I want short-term protection using disk or tape, and online backup if available.

  4. On the Specify Short-Term Goals page select how to you want configure backups to short-term storage. For disk storage you can have 512 express full backups as often as every 30 minutes.

  5. Finish the wizard. Protection will start after the initial recovery point is created.

Recover the database from the secondary server

  1. Rebuild the primary server as a DPM server if required.

  2. To restore the database, in the DPM Administrator Console on the secondary server, click Recovery on the navigation bar.

  3. Browse or search for the protected database. Available recovery points are indicated in bold on the calendar in the recovery points section. Select the date for the recovery point you want to recover. Recover the database to the original location.

  4. After recovering the database run the DPMSync tool. See Recover with the DPMSync tool.

Back up the database to tape

Before you start

You’ll need to know the barcode or tape labels of the tapes that contain a copy of the DPM database. The best way to do this is to schedule a Status Report to be mailed on the same day that the DPM database is backed up. The report will include the last backup date/time, the tape label and the barcode so that you can locate it for recovery. Alternatively you can use this SQL script to extract the information from the current database so you can to store it separately in case of disaster.

Select Path,ro.FileSpec,media.Label,media.BarcodeValue,pd.CreationDate,
pd.ExpiryDate,pd.LifeStatus as "1=valid, 2=expired"
from dbo.tbl_MM_MediaMap mm
join dbo.tbl_MM_PhysicalDataset pd on pd.datasetid = mm.datasetid 
join dbo.tbl_MM_Media media on media.MediaId = mm.MediaId 
join dbo.tbl_RM_RecoverableObjectFileSpec ro on ro.DatasetId = mm.DatasetId
where ro.filespec like '%DPMDB%'
order by CreationDate desc

Configure the backup

  1. In DPM Administrator Console, click Protection on the navigation bar and click New in the Actions pane.

  2. On the Select group members page, if you’re running SQL Server remotely select the remote SQL Server installed and select DPM database. If SQL Server is running on the DPM server expand the DPM server item and select DPMDB.

  3. On the Select data protection method page, select I want short-term protection using tape. Specify the short-term protection policy options. We recommend a retention range of two weeks for DPM databases.

  4. In the Select Library and Tape Details page select the library you want to use for your tape backups. In Drives allocated , select the number of drives you want to allocate for the tape backups. In the Copy library section, if you want to copy data across multiple sites, select the library you want to use for the multiple backup copies.

  5. In the Tape options for long-term protection section, do the following:

    • Select Check backup for data integrity to check for data integrity between the backup copy versions.

    • Select the Compress data option to enable data compression on tape, which reduces the space needed on the tape and increases the number of backup jobs that can be stored on the same tape.

    • Select the Encrypt data option to encrypt the data before it is written to tape, which increases the security for archived data.

    • Select the Do not compress or encrypt data option if you do not want DPM to perform data compression or encryption.

  6. Complete the Create New Protection Group Wizard with the protection options you want to use.

Recover the database from tape

Before you start note the following:

  • The restore process will depend on the tape hardware available and the current state of the DPM server that took the tape-based backup. If you can’t restore the tape from the DPM server that did the backup, you’ll need to restore it from another DPM server that has the same type of tape drive so that the tape can be read. You might need to rebuild the DPM server if the only tape hardware available was the one attached to the failed DPM server.

  • If you’re using DPM tape encryption, you’ll need the same certificate used to encrypt the tape installed on the alternate DPM server.

  1. Locate the physical tape that contains the version/date/time of the DPM database you want to restore.

  2. Insert the backup tape into the tape drive or library and perform a detailed inventory in the DPM console -> Management –>Libraries. Note that If the DPM server you are restoring from is a different DPM server, or it’s a new installation of DPM on the original server, the tape will be shown as imported (not created by this DPM server).

  3. If necessary, recatalog the imported tape.

  4. On the recovery tab, locate the database data source. If it was from an imported tape, the recovery point will be under External DPM tapes.

  5. Recover the database (DPMDB) files. You can select to Recover to any instance of SQL Server or to Copy to a network folder.

  6. After the files are restored from tape, continue with recovery steps using SQL Management Studio or DPMSYNC –RESTOREDB. For more information see Recovery with the DPMSync tool.

Back up with native SQL Server backup to a local disk

You can simply back up the DPM database to a local disk with native SQL Server backup, independent of DPM.

  1. Get an overview of SQL Server backup.

  2. Learn more about backing up SQL Server to the cloud.

Back up with native SQL Server backup to a share protected by DPM

This backup option leverages native SQL to back up the DPM database to a share, protects the share with DPM, and uses Windows VSS previous versions to facilitate the restore.

Before you start

  1. Consider enabling DPM self-service recovery so that SQL Server administrators can access data protected by DPM and restore a DPM SQL Server database. Note that this isn’t required.

  2. If the DPM database is located on a remote SQL Server, install the DPM agent on that server.

  3. On the SQL Server make a folder on a drive with enough free space to hold a single copy of a backup. For example: C:\DPMBACKUP.

  4. Share the folder. For example share C:\DPMBACKUP folder as DPMBACKUP.

  5. Copy and paste the OSQL command below into Notepad and save it to a file named C:\DPMBACKUP\bkupdb.cmd. Make sure there is no .txt extension. Modify the SQL_Instance_name andDPMDB_NAME to match the instance and DPMDB name used by your DPM server.

    OSQL -E -S localhost\SQL_INSTANCE_NAME -Q "BACKUP DATABASE DPMDB_NAME TO DISK='C:\DPMBACKUP\dpmdb.bak' WITH FORMAT"
    
    
  6. Using Notepad, open the ScriptingConfig.xml file located under the ...\DPM\Scripting folder.

    • On a remote SQL Server: C:\Program Files\Microsoft Data Protection Manager\DPM\Scripting

    • On a DPM 2012 R2 server: C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Scripting 

    • On a DPM 2012 or 2012 with SP1 server: C:\Program Files\Microsoft System Center 2012\DPM\DPM\Scripting 

    • On a DPM 2010 server, or on a DPM 2012 server upgraded from DPM 2010: C:\Program Files\Microsoft DPM\DPM\Scripting 

  7. Modify ScriptingConfig.xml and change DataSourceName= to be the drive letter that contains the DPMDBBACKUP folder/share. Change the PreBackupScript entry to the full path and name of thebkupdb.cmd saved in step 5.

    <?xml version="1.0" encoding="utf-8"?>
    <ScriptConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns="http://schemas.microsoft.com/2003/dls/ScriptingConfig.xsd">
    <DatasourceScriptConfig DataSourceName="C:">
    <PreBackupScript>C:\DPMDBBACKUP\bkupdb.cmd</PreBackupScript>
    <TimeOut>120</TimeOut>
    </DatasourceScriptConfig>
    </ScriptConfiguration>
    
  8. Save the changes to ScriptingConfig.xml.

  9. Protect the C:\DPMBACKUP folder or the \\sqlservername\DPMBACKUP share using DPM and wait for the initial replica to be created. There should be a dpmdb.bak in the C:\DPMBACKUPfolder as a result of the pre-backup script running which was in turn copied to the DPM replica.

  10. If you don’t enable self-service recovery you’ll need some additional steps to share out the DPMBACKUP folder on the replica:

    1. ) In the DPM console > Protection, locate the DPMBACKUP data source and select it. In the details section, click Click to view details on the link to the replica path and copy the path into Notepad. Remove the source path and retain the destination path. The path should look similar to the following: C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Volumes\Replica\File System\vol_c9aea05f-31e6-45e5-880c-92ce5fba0a58\454d81a0-0d9d-4e07-9617-d49e3f2aa5de\Full\DPMBACKUP.

    2. Make a share to that path using the share name DPMSERVERNAME-DPMDB. You can use the Net Share command below from an administrative command prompt.

      Net Share DPMSERVERNAME-dpmdb="C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Volumes\Replica\File System\vol_c9aea05f-31e6-45e5-880c-92ce5fba0a58\454d81a0-0d9d-4e07-9617-d49e3f2aa5de\Full\DPMBACKUP"
      

Configure the backup

You can back up the DPM database as you would any other SQL Server database using SQL Server native backup.

  • Get an overview of SQL Server backup.

  • Learn more about backing up SQL Server to the cloud.

Recover the database

  1. Connect to the \\DPMServer\DPMSERVERNAME-dpmdb share using Explorer from any Windows computer.

  2. Right-click the dpmdb.bak file to view properties. On the Previous Versions  tab are all the backups that you can select and copy. There is also the very last backup still located in the C:\DPMBACKUP folder which is also easily accessible.

  3. If you need to move a SAN attached DPM storage pool disk to another server to be able to read from the replica volume, or to reinstall Windows to read locally attached disks, you’ll need to know the DPM Replica volume Mount point path or Volume GUID beforehand so you know what volume holds the database backup. You can use the SQL script below to extract that information any time after initial protection but before the need to restore. Replace the %dpmsqlservername% with the name of the SQL Server hosting the database.

    Select ag.NetbiosName as 
    ServerName,ds.DataSourceName,vol.MountPointPath,vol.GuidName 
    from tbl_IM_DataSource as ds
    join tbl_PRM_LogicalReplica as lr on ds.DataSourceId=lr.DataSourceId
    join tbl_AM_Server as ag on ds.ServerId=ag.ServerId
    join tbl_SPM_Volume as vol on lr.PhysicalReplicaId=vol.VolumeSetID
    and vol.Usage =1 
    and lr.Validity in (1,2) 
    where ds.datasourcename like '%C:\%' -- volume drive letter for DPMBACKUP
    and servername like '%dpmsqlservername%' --netbios name of server hosting DPMDB
    
    
  4. If you need to recover after moving DPM storage pool disks or a DPM server rebuild:

    1. You have the volume GUID, so should that volume need to be mounted on another Windows server or after a DPM server rebuild, use mountvol.exe to assign it a drive letter using the volume GUID from the SQL script output: C:\Mountvol X: \\?\Volume{d7a4fd76-a0a8-11e2-8fd3-001c23cb7375}\.

    2. Reshare the DPMBACKUP folder on the replica volume using the drive letter and portion of the replica path representing the folder structure.

      net share SERVERNAME-DPMDB="X:\454d81a0-0d9d-4e07-9617-d49e3f2aa5de\Full\DPMBACKUP"
      
      
    3. Connect to the \\SERVERNAME\DPMSERVERNAME-dpmdb share using Explorer from any Windows computer

    4. Right-click the dpmdb.bak file to view the Properties. On the Previous Versions  tab are all the backups that you can select and copy.

Recover with the DPMSync tool

You can use the DPMSync tool to restore backups taken by DPM, DPMBackup.exe, and native SQL Server backup. It can restore backups with theee .bak extension, or restore SQL Server database files with the .mdf and .ldf extensions. Note additions to this tool:

  • From DPM 2012 support was added for multiple DPM servers to share one instance of SQL Server for the DPM database.

  • From DPM 2012 R2 support for a SQL Server cluster used as the DPM database.

The result of these changes is an increase for required parameters when you run DPMSync.exe to restore a DPM 2012 database.

The DPMSync.exe utility is installed by default in the DPM installation path inside the bin folder. However, this should already be added to the %path%system variable and can run from any administrative command prompt.

Run the tool as needed.

 

Scenario Instructions Details

Restore the database on a remote SQL Server

C:\Program Files\Microsoft Data Protection Manager\DPM2012\SQLPrep>dpmsync -restoredb -dbloc c:\temp\dpmdb_dpm03.bak -instancename DPMSQLDB -dpmdbname dpmdb_dpm03
DpmSync 2.0 - DPM database synchronization command-line tool
Copyright (c) 2012 Microsoft Corporation. All rights reserved.
Restoring DPM Database completed.

Note that when using the default instance specify (local) or a period (.) for the instance name.

Restore the database on the local DPM server using the .mdf file

C:\>dpmsync -restoredb -dbloc E:\MSDPM2012$DPMDB.mdf -instancename dpmserver\msdpm2012 -dpmdbname dpmdb
DpmSync 2.0 - DPM database synchronization command-line tool
Copyright (c) 2013 Microsoft Corporation. All rights reserved.
Copying file from 'e:\msdpm2012$dpmdb.mdf' to 'C:\Program Files\Microsoft System Center 2012\DPM\DPM\DPMDB\MSDPM2012$DPMDB.mdf.recovered'
Copying file from 'e:\msdpm2012$dpmdb_log.ldf' to 'C:\Program Files\Microsoft System Center 2012\DPM\DPM\DPMDB\MSDPM2012$DPMDB_log.ldf.recovered'
Files copied successfully.
Database detached successfully.
Renamed file 'MSDPM2012$DPMDB.mdf.recovered' to 'MSDPM2012$DPMDB.mdf'
Renamed file 'MSDPM2012$DPMDB_log.ldf.recovered' to 'MSDPM2012$DPMDB_log.ldf'
Database attached successfully.
Restoring DPM Database completed.

If you restore .mdf files to a database that isn’t named DPMDB see Microsoft article 2968666.

Anytime a database is restored from a backup you need to run the DpmSync -Sync command to reconcile backup job run times, DPM storage pool usage and other configuration settings that might have changed since the restored database backup time. The DPMSYNC –SYNC command must be run on the DPM server where it has access to the storage pool and snapshots. A consistency check will be required on all data sources before normal protection can be resumed.

On the DPM server run:

C:\>dpmsync –sync
DpmSync 2.0 - DPM database synchronization command-line tool
Copyright (c) 2013 Microsoft Corporation. All rights reserved.
Note: The DPM role configuration of this server will also be rolled back during this operation.
DPM Synchronization completed.
Your tape library status may have changed.
Recommendation: Go to the Library tab in the Management Task Area of the DPM Administration Console and choose the Inventory Library action.

-----
For additional resources, see Information and Support for System Center 2012.

Tip: Use this query to find online documentation in the TechNet Library for System Center 2012. For instructions and examples, see Search the System Center 2012 Documentation Library.
-----
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2015 Microsoft