Identifying Databases Not Meeting Backup Service Level Agreements (SLAs)

A quick way to find out if your backups are working as you expect

One of the questions I always ask database administrator interviewees is “What is the number one job of a DBA?”.  I feel the answer says a lot about how this possible candidate thinks, and on another level if they read the job description or not.  What I hope to hear is “Ensure recoverability in the event of a disaster” or something along those lines.  If it wasn’t obvious, I feel strongly that recoverability of your database(s) is priority number one.

Because there is an ever-present need to ensure your backups are current and meeting your establish Service Level Agreement (SLA) with the business, it’s probably a good idea to have a handy query that will get this for you.  I also like to operate on the notion that “No News is Good News”, so the output of this report shows only databases failing the SLA.

The Query

If you are unfamiliar with Central Management Servers or Registered Server Lists, now is the time to get one of these put together.  This report will run against any SQL 2005 or newer instance, so if you have SQL 2000 instances, you will need to see what fails established SLAs another way… or just bite the bullet and upgrade the instance.  Open a new multi-server query and execute the following to see what databases are falling outside of your established backup SLA:

DECLARE @minFullSLA INT, @minDiffSLA INT, @minTLogSLA INT

-- Specify current SLA values (in days) for Full, Differential, and TLog backups
SELECT @minFullSLA = 7, @minDiffSLA = 2, @minTLogSLA = 0

-- Missed Backup SLA
;WITH BackupHist
AS 
(
       SELECT
                s.server_name
              , d.name AS database_name
              , m.physical_device_name
              , CASE m.device_type
                     WHEN 2 THEN 'Disk'
                     WHEN 102 THEN 'Backup Device (Disk)'
                     WHEN 5 THEN 'Tape'
                     WHEN 105 THEN 'Backup Device (Tape)'
                     WHEN 7 THEN 'Virtual Device'
                END AS device_type
              , CAST(s.backup_size / 1048576.0 AS FLOAT) AS backup_size_mb
              , s.backup_start_date
              , s.backup_finish_date
              , CASE s.[type]
                     WHEN 'D' THEN 'Database (Full)'
                     WHEN 'I' THEN 'Database (Differential)'
                     WHEN 'L' THEN 'Transaction Log'
                     WHEN 'F' THEN 'File or Filegroup (Full)'
                     WHEN 'G' THEN 'File or Filegroup (DIfferential)'
                     WHEN 'P' THEN 'Partial (Full)'
                     WHEN 'Q' THEN 'Partial (Differential)'
                END AS backup_type
              , s.recovery_model
              , ROW_NUMBER() OVER(PARTITION BY s.database_name, s.[type] ORDER BY s.backup_start_date DESC) AS Row
       FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m
              ON s.media_set_id = m.media_set_id
              RIGHT OUTER JOIN sys.databases d
              ON s.database_name = d.name
              AND s.recovery_model = d.recovery_model_desc
       COLLATE SQL_Latin1_General_CP1_CI_AS
)
SELECT *
FROM BackupHist
WHERE (Row = 1
       AND (
                     -- FULLs older than @minFullSLA days will pop
                     (backup_type = 'Database (Full)' AND backup_start_date < GETDATE()-(@minFullSLA + 1))
                     OR
                     -- DIFFs older than @minDiffSLA days will pop
                     (backup_type = 'Database (Differential)' AND backup_start_date < GETDATE()-(@minDiffSLA + 1))
                     OR
                     -- TLOGs older than @minTLogSLA days will pop
                     (backup_type = 'Transaction Log' AND backup_start_date < GETDATE()-(@minTLogSLA + 1))

              )
       )  -- Nonexisting backups for new dbs
        OR (backup_type IS NULL AND database_name <> 'tempdb')

Final Thoughts

This is a nice little query to tell you which databases fall out of your established SLA.  This report becomes more important if your environment has multiple backup methodolgies in effect.  If you see anything missing, let me know as I always welcome feedback.

– John

References:

http://technet.microsoft.com/en-us/library/bb418967.aspx
http://technet.microsoft.com/en-us/library/bb895144.aspx
http://technet.microsoft.com/en-us/library/ms190631.aspx