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, d.name 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, d.Recovery_model_desc, bmf.physical_device_name 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 = d.name WHERE d.name <> 'tempdb' ORDER BY d.name, bs.type ;
This is what I get from this query:
- 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.
- The last backup date and time for each backup; did my backup process work overnight? Is the transaction log backed up regularly?
- 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?
- 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.