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
October 27, 2020 at 12:22 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 27, 2020 at 3:41 pm
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.
October 27, 2020 at 6:18 pm
Thanks.
If a 3rd party tool takes db backups(full,diff,log) will that information be captured inside sql tables ?
October 27, 2020 at 6:38 pm
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/
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 27, 2020 at 8:34 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply