Finding Failed Backups And Jobs

Thomas Rushton builds a nasty query to answer an important question:

Assumptions

  1. Backup jobs that do full backups don’t overlap
  2. There’s nothing else doing full backups
  3. erm…
  4. that’s it

The reason I was thinking about this is that we have occasional-but-annoyingly-frequent backup job failures, wherein most of the databases back up just fine, but the odd one fails. (SharePoint box, I’m looking at you…) Rather than trawling through the error logs to find out which particular database didn’t back up successfully, I wanted a query to do the heavy lifting. Yes, I’m a lazy lone DBA…

The end result is a pivoted query showing days in which full backups fail.  There’s a lot of information in there, so that might be something I’d want to visualize in Excel or R, changing cell colors for failed jobs so they stand out better.  Nevertheless, check this out, especially if you don’t have a solution in place to monitor backups.

Related Posts

Alerting On Missed Log Backups

Jamie Wick shows us how to build out an alert if we’ve exceeded a threshold for time since the last log backup: The primary reason most transaction logs start to (unexpectedly) grow is that the transaction log hasn’t been backed up. This is usually caused by one of two situations: the log backup job failed, […]

Read More

Azure SQL Data Warehouse Restore Points

Arun Sirpal explains how backups work with Azure SQL Data Warehouse: The question is how are backups done with Azure SQL DW? It is very different from Azure SQL DB (which you would expect). Azure SQL DW has a totally different architecture to its classic database counter-part. Restore points are the key here. Automatic ones […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031