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.
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.
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.
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 firstname.lastname@example.org 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