Loading . . .
TSQL #66 – Monitoring Backup Solution – Backup History

TSQL #66 – Monitoring Backup Solution – Backup History

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 

  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

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.

Backup History Monitoring

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).

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…


One thought on “0

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Microsoft Business Intelligence and Analytics Previous post New data visualization capabilities in Excel 2016
Backup Button Next post SQL Server Backup throws Error Message – Msg 3201 Level 16 State 1

SQL from Hamburg

Das bin ich ;-)

Björn Peters - MVP - Data Platform
I had to do with MS SQL databases for the first time in 2000 and looked after these database systems for around seven years. From 2007 to 2019, I was employed as a database administrator and looked after many different SQL servers from medium-sized companies and large corporations from different industries.
Although I have some certificates, I get my insights and knowledge about the SQL Server purely from day-to-day business, reading / following numerous forums/blogs.
I'm not specialized in any topic, but I focus mostly on performance analysis.
Since the end of 2016, I have been the Azure Meetup Hamburg organizer and from April 2017 to June 2018, Cloud and Data Center Management MVP, and since July 2018, Data Platform MVP.