TSQL #66 - Monitoring Backup Solution

From multiple occasion I also want to write something on the subject of backup history, first of all because Catherine Wilhelmsen has hosted the TSQL Tuesday # 66 and secondly Thomas Larock has introduced a similar solution just today.

We DBAs knowing the vexing issue of backup monitoring … did it run or did not run, can I keep my customer SLA yet?

We have agreed to our customers contractually to check the sql server backup on a daily base, it has to be ran successfully within the last 24 hours, at least one full backup. If this is not the case our reporting tool will created an incident ticket “Please check Backup” automatically.
Therefore my statement has also some limitations which can of course be individually adjusted depending on your needs.
Ideally, you write the query result into a table to collect historical data of the backup, so you can identify a trend of the respective backup sizes.

/*
Get all Backup History Data - used at Customer XYZ for Reporting 
*/

SELECT 
  s.name as [Database], 
  (select bmf.physical_device_name from msdb..backupmediafamily bmf where bmf.media_set_id = bs.media_set_id) as [Backup_Path_File],
  bs.backup_start_date as DATE_BEGIN, 
  bs.backup_finish_date as DATE_END, 
  CASE WHEN bs.backup_start_date > DATEADD(dd,-7,getdate())
    THEN 0
    ELSE 1
  END as [Status],
  CASE WHEN bs.backup_start_date > DATEADD(dd,-1,getdate())
    THEN 'Backup is current within a day'
     WHEN bs.backup_start_date > DATEADD(dd,-7,getdate())
    THEN 'Backup is current within a week'
    ELSE '*****CHECK BACKUP!!!*****'
  END as [Message],
  convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as [Backup_Volume],
  GETDATE() as [Date_Checked]
from 	master..sysdatabases s LEFT OUTER JOIN	msdb..backupset bs ON s.name = bs.database_name
  AND bs.backup_start_date = (SELECT MAX(backup_start_date) FROM msdb..backupset WHERE database_name = bs.database_name AND type = 'D')
WHERE	s.name <> 'tempdb'
ORDER BY s.name
GO

With the result of the query, you now can do a lot of things, for example based on the column “Status” mark the appropriate line colored to make the faulty backups more visible or to change the subject line of a status-mail.
We have a combination of SQL Server alerts with the above query script which then informs us where we check/restart the backups.

In addition I recommend to establish the SQL Alert “SQL Server Alert System: ‘Severity 016’ occurred on SERVER_XYZ” with mail alerting (see also this blog post for SQL Server Alerts).

addendum:
Last week I stumbled over a storage problem where this script is not really worked because the backup was running but indeed it was unfortunately very slow, so that it had not been finished within 40 minutes (as usual) but unfortunately had run around 24 hours… the result was not telling the whole truth but in this case we knew about the problem…

Björn Peters

Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.