The Greatest Backup Report

In some organization an important Key Point Indicator (KPI) has to do with backup success rates. This KPI attempts to measure what percentage of backups have taken place. However, many times this KPI has not been clearly defined.

I always ask "what constitute a backup failure"?  If a backup fails to take place but I fix it the next morning, is that still a fail backup?  or if a Full Backup fails but the Tlogs backups still take place, so if a failure occurs I'm still able to recover with no data loss, is that still a failed backup? This is the equivalent of if a tree fails in the forest, but I repair it without anyone knowing, did it still fall?

If we are reporting against SQL Jobs success rates, then we can run into a situation where if the job is misconfigured and it fails to account for newly created databases, we could run into a situation where we have 100% success rate but have databases with no backups.

Some might argue that, using the true definition, "a successful backup is one that you could restore".  This is fine and dandy, but how often are we able to restore all our database backups in a daily manner?

As you can see, there areextremes, but we still need to provide an honest measure for the KPI.

A good compromise would be to report against the actual backup as recorded in MSDB versus the expetected schedule of said backup

In this sense, we are not interested just on the last backup, but we are interested to know if all backups are running at regular intervals, and what that interval is.

This is a first iteration of the script where for a single DB might look like this:

Based on the results, we can assume that the Backup Schedule probably is as follow:
Full 7 days.
Diff 1 day.
Tlog: Hourly.

We can also assume that the first value for Full is 5.  Since 5 is less than 7, not an issue,  still within schedule.
The last  value for Diff is 2.  Since 2 is more than 1, possible and issue. However, it probably means that a full backup was taken instead.
There is a value Tlog that is 2. Since 2 more than 1, there was a missed backup in that time period.

Another example:

We can tell that FULL failed, since the first value is 12, and 12 is more than 7, therefore a Full Backup is missing.
We can also tell that DIFF failed, since the first value is   2, and   2 is more than 1, therefore a Diff Backup is missing.

Last Example:

In here, we can see that someone was taking backups outside our normal backup schedule, sine most backups are going to TDP and we have a few backups going to local disk.

Finally, the script itself:

The Script is fully customizable to report for a certain number of days and either all databases or just one:

/*
DESCRIPTION	
List Database Backup Age, per database, and the intervale between backups

CONFIGURATION	top
SET @LastXDays for how far back you query MSDB
Set @DiffDays if you want to limit Diff.
Set @TlogDays if you want to limit the Tlog 

Set @dbname to NULL for all DBs, or to a single DB

Author		 mquinta@us.ibm.com

Compatibility list: 
MSSQL2005
MSSQL2008
MSSQL2012


*/

DECLARE @dbname varchar(500)
DECLARE @LastXdays nvarchar(2)
DECLARE @TlogDays int
DECLARE @DiffDays int

-- Configuration
--  how much history we query
SET @LastXdays = 28

-- limt the results for Differential backups
SET @DiffDays = 7
-- Limit results for Tlogs. Recommended 1 or 2 days
SET @TlogDays = 1  

-- SET database. If Null, it'll do all databases
SET @dbname = null  
-- SET @dbname = 'adventureworks'
----  end configuration


;WITH BackupAgeTable AS
(  
-- query info for backups, and place it in a 
-- Common Table Expression, adding a row_number row.
SELECT
	 s.database_name
	,s.backup_size
	,s.backup_start_date
	,s.type
	,m.physical_device_name
	,m.device_type
-- Ordering will group by datbase, then type of BK and then sort by date
	,row_number() OVER (ORDER BY s.database_name,
								 s.[type],
								 s.backup_start_date DESC)
						AS rownumber
FROM SYS.DATABASES d   
INNER JOIN msdb.dbo.backupSET s ON d.database_id = db_id(s.database_name)
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_SET_id = m.media_SET_id
WHERE
	1=1
	and DATEDIFF(dd,(s.backup_start_date),GETDATE()) < = @LastXdays
	and s.database_name like coalesce (@dbname,'%')
		
)

SELECT 
	cur.database_name
	, CASE cur.type
		WHEN 'D' THEN 'Full'
		WHEN 'I' THEN 'Diff'
		WHEN 'L' THEN 'TLog'
		else 'other'
	END AS backup_type
	, cast(cur.backup_size/1024/1024 as numeric (10,1)) 'SizeMB'
	, CASE cur.device_type
		when 2 then 'Disk'
		when 7 then 'Virtual' -- Like TDP
	end as 'DeviceType'
	, cur.physical_device_name
	, cur.backup_start_date
-- Using COALESCE because first row will be NULL and we need backup age instead
	, case cur.type
		when 'D' then 
		COALESCE (DATEDIFF(dd,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(dd,CUR.backup_start_date,GETDATE()))
		when 'I' then
		COALESCE (DATEDIFF(dd,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(dd,CUR.backup_start_date,GETDATE()))
		when 'L' then
		COALESCE (DATEDIFF(hh,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(hh,CUR.backup_start_date,GETDATE()))
		else 'other'			
	END as 'interval of Backups'
-- Using CASE statement because we want HOURs for Tlogs Backups and DAYS for the rest	
	, case cur.type
		when 'D' then 'days'
		when 'I' then 'days'
		when 'L' then 'hours'
		else 'other'
	END as 'units (days/hours)'

FROM BackupAgeTable CUR
-- Left join over itsef but with a offSET of one row
-- to be able to make a DIFF between dates of two consecutive rows
LEFT join BackupAgeTable PREV 
	on CUR.rownumber = (PREV.rownumber+1)
	   and cur.database_name=prev.database_name
	   and cur.type = prev.type 
where 1=1
-- Clever filtering to limit resutls
      and(   (cur.type = 'D')-- and datediff(dd,cur.backup_start_date,getdate())< @LastXdays)
	  or (cur.type = 'I' and datediff(dd,cur.backup_start_date,getdate())< @DiffDays )
	  or (cur.type = 'L' and datediff(dd,cur.backup_start_date,getdate())< @TlogDays )
	 )

-- The UNION ALL checks for DBs with no backups
UNION ALL
SELECT DISTINCT
	  SD.name 'database'
	, 'NO BACKUPS' 'TYPE'
	, NULL 'SizeMB'
	, NULL 'DeviceType'
	, NULL 'PhysicalDevice'
	, NULL 'BackupStartDate'
	, NULL 'Interval'
	, 'NO BACKUPS'	'Units'
FROM 
	 master..sysdatabases as SD

LEFT JOIN msdb..backupSET bp on SD.name = bp.database_name 
		 AND DATEDIFF(dd,(bp.backup_start_date),GETDATE()) <= @LastXdays
WHERE 1=1
and bp.database_name is NULL
and sd.name not in ('tempDB','model')


ORDER BY 8 DESC ,1,2, 6
-- First order by column 8 so databases with no backups
-- are listed on top