Tracking your Backups

Tracking your backups…one of the most basic/mundane/important functions for which a DBA is usually responsible.  How do you know that your databases are being successfully backed up? What about your transaction logs?

If you don’t have a current backup (even for a development database!), you and your users could be “up the creek without a paddle”!  I suggest that you monitor backups every day…so make the monitoring as simple as possible so that you can move on to other tasks.  There is really no excuse for NOT doing this.

I have performed this task using many different tools over the years, each seemed to have one shortcoming or another.  At my current job we use SQL Agent jobs to perform the backups and we monitor the success of each job daily using a combination of scripts and tools.  Emails are sent noting any job failures.  This works pretty well, but there are still some holes in our monitoring.  Sometimes the backup jobs never complete (yes, that happens more often than you think!).  Simple monitoring can determine if you have a valid backup file or if you need to re-run a job.  If the recovery model is full am I backing up the transaction log, or letting the log size run amuck?

I decided I needed another reporting script to fill in some of these holes, so I created the one listed below.  Instead of monitoring the backup jobs, this query monitors the backup completion and existence of a backup file.  This query will give you the last successful backup for every database on your server, and will note “NONE” if there is no backup completion.  It uses the backup history tables in MSDB:

--last Database Backups for all databases 
-- s henry 5/15/2017
SELECT SERVERPROPERTY('Servername') AS ServerName, AS Database_Name,
      CASE WHEN bs.backup_start_date IS NULL THEN 'NONE'
            ELSE CAST(bs.backup_start_date AS VARCHAR) END AS LastBackupDate,
      CASE bs.type
           WHEN 'D' THEN 'Database'
           WHEN 'L' THEN 'Log'
           WHEN 'F' THEN 'FileGroup'
           WHEN 'I' THEN 'Differential database'
           WHEN 'G' THEN 'Differential file'
           WHEN 'P' THEN 'Partial'
           WHEN 'Q' THEN 'Differential partial'
      END AS backup_type,
FROM msdb.dbo.backupset bs 
LEFT JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id 
JOIN (SELECT bs2.database_name, bs2.type, MAX(bs2.backup_start_date) as MaxStartDate
      FROM msdb.dbo.backupset bs2 
      WHERE bs2.backup_finish_date is not null  -- make sure the backup completed
      GROUP BY bs2.database_name, bs2.type
     ) m ON bs.database_name = m.database_name
          AND bs.type = m.type
          AND bs.backup_start_date = m.MaxStartDate
RIGHT JOIN master.sys.databases d ON bs.database_name =
WHERE <> 'tempdb'
ORDER BY, bs.type ;


This is what I get from this query:

  1. A list of every database on the server and its latest backup; am backing up every database? If the query indicates NONE then you should be backing up this database.
  2. The last backup date and time for each backup; did my backup process work overnight? Is the transaction log backed up regularly?
  3. Recovery model and backup type; if my recovery model is full am I backing up the transaction log on a regular basis? If simple, should it be simple?
  4. Physical location; am I storing the backups in the right place and using the right name?

You can pop this query into your existing database monitoring “framework” pretty easily, whether you just run it in SSMS, use PowerShell, SSRS, SSIS, or some other method of monitoring/reporting.  You can also change it to fit your needs; there is much more information on the backup history tables that you can track regarding backups.  I encourage you to check out the Books Online and take a closer look at the information available and modify this query to suit your needs.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s