identify sql backup strategies

  • how do I identify if the dbs are getting backed up (full,diff,log) without looking through sqljobs /maintenance plans etc..as they maybe getting backed up from a 3rd party tool. Any tables I can look through

     

    • This topic was modified 1 month ago by  mtz676.
  • You can look at the backup tables in MSDB, backupset. There's a lot of information there on the type of backup, when it occurred, if it was successful, etc. However, that's not going to tell you what the intention of the backup was, how often it's supposed to be run, or anything else. That information should be in the code in your Agent jobs, 3rd party tool, what have you. Further, somewhere there should be an Service Level Agreement (SLA) document with the business that defines Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO). Combining the RPO, RTO, and the code will tell you what the backup strategies are. The information from MSDB will help you identify if the backup strategies are working to meet the SLA.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • We have a 3rd party tool, Datto, that does server "snapshot" backups. SQL registers it as a database backup, although it did not take a real backup. I found it confusing and giving a false impression that SQL databases were being backed up by that tool.

     

    For my purposes, when querying backup information, I added "backupmediafamily.device_type = 2 " to my WHERE clause to show backups to disk, not the virtual backups. YMMV.

    • This reply was modified 1 month ago by  homebrew01.
    • This reply was modified 1 month ago by  homebrew01.
  • Thanks.

    If a 3rd party tool takes db backups(full,diff,log) will that information be captured inside sql tables ?

  • mtz676 wrote:

    Thanks.

    If a 3rd party tool takes db backups(full,diff,log) will that information be captured inside sql tables ?

    If it is taking a SQL backup.  It will be logged in the backupset table like Grant suggested.

    Also, you do not need a backup strategy.  You need a restore strategy.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • mtz676 wrote:

    Thanks.

    If a 3rd party tool takes db backups(full,diff,log) will that information be captured inside sql tables ?

    Yes, as long as they're actually taking SQL Server backups. There are some storage technologies that use the mechanics of disk snapshotting at the hardware level to do transactionally aware backups, but are not in any way correlated to a SQL Server backup. For example, Redgate Software, my employer, makes a backup technology. In part, it uses SQL Server backups to do what it does. Therefore, all its backups will be in the msdb table.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Michael L John wrote:

    Also, you do not need a backup strategy.  You need a restore strategy.

    MY HERO!!!!

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply