none
Protecting DPM's SQL server/DB. RRS feed

  • Question

  • Hi,

    I am using DPM 2012 R2 on Windows 2012 R2 and have SQL 2012 server. The scenario is SQL server is used by other applications as well. DPM is used to backup the SQL server but DPM itself uses the same SQL server for its DB. My question is, if the SQL server DPM uses as backed gets corrupted, how DPM will backup previous working version of SQL? What is the best practise to protect the SQL server that DPM uses to store its config and other settings?

    Thanks!

    Thursday, May 1, 2014 8:48 PM

All replies

  • Hi,

    NOTE: This works best if you enable DPM End User Recovery.

    1) On the Server hosting the DPM Database (This may be DPM Server itself) create a folder called DPMDBBACKUP in a location of your choice.  This will be used to store a SQL native backup of DPMDB that will be protected by DPM.
    2) Share out the DPMDBBACKUP folder.
    3) Open notepad then copy the following line and paste it into the notepad window.

    osql -E -S localhost\MSDPM2012 -Q "BACKUP DATABASE DPMDB_NAME TO DISK='C:\DPMDBBACKUP\DPMDB.BAK' WITH FORMAT"

    4) Modify the SQL instance name to match the instance hosting the DPMDB.  Change the DPMDB_NAME to match the DPMDB name used by the DPM Server. Change the Path to the location of the DPMDBBACKUP folder you created in step 1.
    5) Save the contents of notepad as BKUPDB.CMD in the DPMDBBACKUP folder you created in step one.
    6) Using notepad, open the ScriptingConfig.xml file located under the ...\DPM\Scripting folder. 

    This would be C:\Program Files\Microsoft Data Protection Manager\DPM\Scripting on a remote SQL server
    This would be C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Scripting on a DPM 2012 R2 DPM server.
    This would be C:\Program Files\Microsoft System Center 2012\DPM\DPM\Scripting on a DPM 2012 or SP1 DPM server.
    This would be C:\Program Files\Microsoft DPM\DPM\Scripting on a DPM 2010 or an Upgraded DPM server from DPM 2010.

    7) Modify the ScriptingConfig.xml and change the DataSourceName= to be the drive letter that contains the DPMDBBACKUP folder / share.  Change the PreBackupScript entry to the full path and name of the bkupdb.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 the ScriptingConfig.xml
    9) Now protect the DPMDBBACKUP folder / share with DPM and prior to making the recovery point, SQL will make a DPMDB database backup in the folder, then DPM will protect that.  This file will be overwritten each time on the share so minumal space is required.
    10) After the recovery point it made, a share will be made on the DPM Server in the form of ProtectedMachineName_sharename and you can access it remotely. Once you have several recovery points, if you connect to the share and right-click the DPMDB.BAK file and under properties - look at the Previous Versions tab.
    11) Copy the dpmdb.bak file from the time you want to restore to.
    12) Run DpmSync.exe -restoredb ... to Restore the DB. followed by DPMSYNC -SYNC


    Other options include protecting the DPMDB and use the online option to backup to Azure for DR solution. DPM online backups support SQL databases including its own DB.  Disaster recovery steps are not eloquent, but see this post.

    Azure recovery from different server
    http://social.msdn.microsoft.com/Forums/en-US/windowsazureonlinebackup/thread/9614bf2a-df71-4d70-9003-1cc1640fd4ec


    You can also use DPM tape backup, and after a DPM rebuild, you can import the tape and recatalog it,restore the DPMDB to a local folder, then Run DpmSync.exe -restoredb ... to Restore the DB followed by DPMSYNC -SYNC

    You can use a Secondary DPM Server to protect the DPMDB.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Saturday, May 3, 2014 12:11 AM
    Moderator
  • Hi Mike,

    You again suggest to use the same DPM's SQL server to backup DPMDB which it is using. My question is what if the SQL server that holds the DPMDB goes down. Do we need to keep DPMDB backup to remote location safely so that if DPMDB and/or DPM server go corrupt we can use the remote copy? What are the best practices to restore DPMDB server if it goes down/corrupts or has a H/w failure? How can we possibly use the same DB to protect DPMDB which DPM is using for config??

    Thanks!

    Sunday, May 4, 2014 3:56 AM
  • Hi,

    Afraid not, I suggested backing up the dpmdb as a file share data source so that it is accessible on the DPM replica volume even if DPM itself is down due to the SQL server failure or the dB is corrupt. Step 9. Backs up the share containing the dpmdb and makes it accessible via a network share on the dpm server which should be available from any other machine even if dpm is not working due to SQL failure.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.


    Sunday, May 4, 2014 4:12 AM
    Moderator
  • Thanks Mike. For now, I am protecting the SQL DB with WSB by backing all the DB and log files to an iSCSI LUN.

    Hope that should recover the SQL DB when needed.

    Wednesday, May 7, 2014 9:15 AM