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

This article describes how to run a single TSQL script against a list of SQL Servers.

It can be re purpose to run any number of script against a set of SQL servers.

1.  Create a list of servers, place them on a file, and name it server_list.txt
   

The servers should be listed one per line.  You can use any string used to connect to SQL server, and enter port number if needed.

The only prerequisite is that the account running SQL Agent must have sysadmin access to all servers on the list.

2. Create a Batch file, name it run_sql.cmd 
   

The content of the Batch file should look like this:

Batch File:

REM: This batch file runs a SQL scripts against the SQL server 
setlocal 
C:
cd "C:\Program Files\Microsoft SQL Server\bk_report"
REM The echo is to set the headers of the resulting CSV file
echo server_name,SQLVersion,ServicePack,Database,RecoveryMode,FullBackupAge,LogBackupAge,DiffBackupAge, > "C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv"

for /F %%a in (server_list.txt) do (
sqlcmd -S%%a -dmaster -E -m 1 -h-1 -idetail_report.sql -s"," -W -w700 >> "C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv"
)
 
end

3. Place a script with your backup report, name it detail_report.sql.
   

  You can use this script:  http://www.cookingsql.com/2014/10/backup-report/

HINT HINT This is the place where you can put pretty much any script and it will run against all your servers. Not only backup scripts, but also anything that you want to collect information from. Think about it like a poor's man Central Management Server.

4. Configure a SQL Job to run the CMD file.
   

5. Add a second step to email the report.
   

The code for the second step is:

     
    USE msdb
    EXEC sp_send_dbmail
    @profile_name='DBA', -- Please note, this Profile Must exist
    @recipients='DBA_GROUP_Email@us.company.com',
    @subject='Backup Report',
    @body='Please review the attachments. This is an automatic email generated from {server_name}',
    @file_attachments='C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv'

6. Schedule the job to run daily.
   

The most useful script.

There is a newer version below, that displays results in a better way

Plug this in a SQL job and send the results via email and you have a flexible reporting tool: click here for instructions

/*
DESCRIPTION    List the age of the last backup for all databases  for FULL, DIFF and Tlog

Plus databases with no backups ever


CONFIGURATION   none

 
Compatibility list:
MSSQL2005
MSSQL2008

 */



set nocount on
use msdb
 
Select Distinct
    serverproperty('servername')
    , serverproperty('productversion')
    , serverproperty('productlevel')
    ,convert(varchar(30),e.database_name) as DBname
    ,convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery')))
    ,(Select datediff(dd,Max(backup_finish_date), getdate()) From backupset a Where a.database_name=e.database_name --and a.server_name  = @@servername
        and type='D'  Group by a.database_name) Full_Bk
        ,(Select datediff(dd,Max(backup_finish_date), getdate()) From backupset b  Where b.database_name=e.database_name
              --and b.server_name  = @@servername
              and type='L' Group by b.database_name) Log_Bk
       ,(Select datediff(dd,Max(backup_finish_date), getdate())  From backupset c Where c.database_name=e.database_name --and c.server_name  = @@servername
              and type='I' Group by c.database_name) Diff_Bk
From backupset e

Where
    e.database_name Not in ('tempdb','pubs','northwind','model')
    and databasepropertyex(database_name,'Status') = 'online'
    and e.database_name in (Select Distinct name from master..sysdatabases)
    --and e.server_name = select @@Servername
 
-- never backed up
Union all
select Distinct
    serverproperty('servername')
    , serverproperty('productversion')
    , serverproperty('productlevel')
    ,convert(varchar(30),name) as DBname
    , 'Never Backup, EVER'
    ,NULL
    ,NULL
    ,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2

This is another script, that better displays the missing backups and TLOG intervale of backups. ( source: LINK )

/*
DESCRIPTION    List the age of the last backup for all databases  for FULL, DIFF and Tlog
Plus databases with no backups ever
CONFIGURATION   none
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
 */
SELECT
  DISTINCT
        a.Name AS DatabaseName ,
        CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), datediff(dd,MAX(backup_finish_date),getdate()), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'D'
                            AND is_copy_only = '0'
                 ), 'No Full') AS 'Full (days ago)' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), datediff(dd,MAX(backup_finish_date),getdate()), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'I'
                            AND is_copy_only = '0'
                 ), 'No Diff') AS 'Diff (days ago)' ,
        CASE DATABASEPROPERTYEX(a.name, 'Recovery')
        WHEN 'SIMPLE' THEN '--'
        ELSE
        COALESCE(( SELECT   CONVERT(VARCHAR(20), datediff(hh,MAX(backup_finish_date),getdate()), 120)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'L'
                 ), 'No Log') 
        END AS 'LastLog Hours Ago' ,
        CASE DATABASEPROPERTYEX(a.name, 'Recovery')
        WHEN 'SIMPLE' THEN '--'
        ELSE
        COALESCE(( SELECT   CONVERT(VARCHAR(20), datediff(hh,backup_finish_date,getdate()), 120)
                   FROM     ( SELECT    ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
                                        backup_finish_date
                              FROM      msdb.dbo.backupset
                              WHERE     database_name = a.name
                                        AND type = 'L'
                            ) withrownum
                   WHERE    rownum = 2
                 ), 'No Log')
        END AS 'LastLog2 hours ago'
                 
                 
                 
FROM    sys.databases a
        LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE   a.name <> 'tempdb'
        AND a.state_desc = 'online'
GROUP BY a.Name ,
        a.compatibility_level
ORDER BY a.name