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

SQL Server Backups To Azure Blob Storage

Kevin Hill shows  how to configure SQL Server to back up a database to Azure blob storage: Note the “no blobs found” in the container.  After a successful backup, you will see it here. Click on ‘Container Properties’ to get the URL for this specific container…this will be used in Backup and Restore statements.  Click the […]

Read More

VSS Snapshot: Freeze & Thaw

Erik Darling points out that VSS backups aren’t instantaneous and can block queries: Ah, backups. Why are they so tough to get right? You start taking them, you find out you’re not taking enough of them, or that they’re not the right kind, or that you’re not using checksums or compression, or that you’re not […]

Read More

Categories

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