asked
Failed backups are not logged in MSDB?
I was trying to create query which returns the number/percentage of failed backups. Since I don't want to be dependant on third-party software, I am digging the MSDB database for this answer. However, I can't find this information. It seems an aborted backup is doing a rollback or am I wrong?
The site runs off of voting. Please mark all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, mark that answer by clicking on the check mark.
Failed backups are not stored there. You'd need to record that based on the failure report from whatever called the backup. I'd assume that would be SQL Agent in the majority of cases. You can capture the failed jobs there.
From personal experience over the last few days, failed RESTOREs don't get logged in MSDB either...
Have you looked at using master..sysdatabases and join on the msdb..backupset to find the successful backups say the last 24 hours if you backup daily. From that you can see which ones were not backed up within the last 24 hours.
Yeah, error log might have some things, but you're better off looking to where they are running from for a count of failures.
It's not that I want to see which databases don't have a backup, i want to measure the number of failed backups. Which seems to be impossible by using the msdb. I know i can query sql jobs, or 3rd party software, but my goal was to create a query to retrieve this answer ( as part of an intake script for new servers ) . But I might check the errorlog for this!
16 People are following this question.
