Is it SQL Backups and DPM or DPM? RRS feed

  • Question

  • Our server team is currently using DPM in our Hyper-V environment and VEEAM in our vSphere environment. The server team is primarily concerned about system recovery. DBA also uses SQL Server backups to insure a single database can be restored to a point in time. Examining the SQL Server table msdb.dbo.backupmediafamily, we can see VEEAM is now configured to use copy only (COPY_ONLY=1) backups while DPM is not (COPY_ONLY=0). I'm about to request that we use the DPM option "Just before a recovery point" that is supposed to result in copy only backups.  I don't fully understand the implications for the system backups and for database backups. 

    Are the system and SQL Server backup chains shared so that it's a choice is one or the other?  For example, if DPM is configured for COPY_ONLY=1, DPM can only do full system backups (i.e., no incrementals)?  A database point-in-time restore has to be done via traditional SQL Servers restores? 

    If DPM is configured with COPY_ONLY=0, incremental backups can be used to restore a database to a point in time?  A traditional SQL Server point-in-time restore can't be used?  A traditional SQL Server backup without the COPY_ONLY option will invalidate DPM restore chain? 


    Randy in Marin

    Monday, April 11, 2016 8:15 PM

All replies

  • I was revisiting this topic and thought I do a search for new info.  I started reading this an thought, "Hey, this is exactly what I'm looking for...."  Then I realized it was me.  Also realized this has not been answered after all this time.

    It's either DPM or SQL backups, not both.  When picking the method to support database point-in-time restores, select one method.  There is one backup chain per database in SQL Server.  I wish each product could have its own backup chain.  I found a useful article targeted to DBA re DPM. 

    The good news is that DPM will backup the full system, including SQL databases, either way.  If you also use DPM to support database PIT restores (e.g., via SSRT), then stick with that and insure DBA only does COPY_ONLY backups.  (Otherwise, it might be hard to find all the transaction log backups required for a PIT restore.)  

    If DBA is responsible for PIT restores and DBA uses traditional SQL backup jobs, then DPM must be COPY_ONLY=1.  Best of both worlds - a full system restore with a working SQL Server plus PIT restores by DBA as needed.  

    I will consider SSRT.  However, we have vSphere and VEEAM as well, so I'm not inclined to switch from SQL backups yet.  It could be cumbersome to use traditional SQL backups on some servers, SSRT for others, and the VEEAM tools on others.  And our T-SQL maintenance jobs just keep working year after year, with almost no effort or update.  Why am I considering giving that up?  Maybe I'm losing my mind...I've been a DBA long enough for that happen....

    I'm a DBA, not a backup administrator.  Please feel free to correct me if I'm wrong.  

    BTW, you can look at the db backup history in MSDB to see if a backup copy_only is 0 or 1.  The system backup will be listed as a "virtual" device.  If you see 0's for both virtual and sql backups of a database, it could be trouble.  

    Randy in Marin

    Saturday, September 9, 2017 12:01 AM
  • DPM like Veeam are good backup tools - they are not SQL Server DB maintenance tools. If you are a DBA you will typically use SQL Server maintenance plans to perform complete/complex database maintenance and backups. In cases like this I typically let DPM pickup the SQL Server disk backups and leave the SQL server database care and feeding to the DBA.

    Sassan Karai

    Monday, October 2, 2017 2:57 PM