Monitoring Your Backups [T-SQL Tuesday #66]

Cathrine Wilhelmsen (b|t) is hosting this month’s T-SQL Tuesday, and the topic she chose is monitoring. All of us, whether a DBA or a developer, internal resource or consultant, monitor something on a daily basis. The hardest part for me was deciding what to write about. In the end, I decided to write about monitoring backups.

When I first started as a DBA, I hadn’t had any formal training on how to do the job, but I knew that backups needed to happen regularly. I created my maintenance plans, then set alerts on the jobs to tell me if they failed. Good enough, right? Wrong. Setting an alert on the job is better than nothing, but there’s so much more to know about your backups.

There are five tables in the msdb database that contain the history of all backups on your system:

• dbo.backupfile
• dbo.backupfilegroup
• dbo.backupmediafamily
• dbo.backupmediaset
• dbo.backupset

backupset is often the most useful table to begin exploring – it contains one row for each backup taken. backupfilegroup and backupfile tell you about the database’s filegroups, data files, and log files that were backed up.  Similarly, backupmediaset and backupmediafamily describe the backup files themselves.  (Books Online has a complete reference for all of these tables.)

Put all of these together and you have a wealth of information about what was backed up, how, and where. That data can then be used for both snapshot reports and for trending. For example, one report I’ve used frequently simply lists the last time a database was backed up:

with backupData as
    (select bs.database_name, bs.backup_finish_date as last_backup_date, bmf.physical_device_name as backup_file_name, ROW_NUMBER() OVER(ORDER BY bs.backup_set_id DESC) as rowId
        from dbo.backupset bs
            inner join dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id
                                                    and bs.type = 'D'
                                                    and bs.is_damaged <> 1
                                                    and bs.has_incomplete_metadata <> 1
                                                    and bs.is_copy_only <> 1)
select database_name, last_backup_date, backup_file_name
    from backupData
    where rowId = 1

I’ve also used these tables to provide a quick way to trend the sizes of my databases:

select bs.database_name, cast(bs.backup_start_date as date) as backup_date, bs.backup_size, bs.compressed_backup_size
    from dbo.backupset bs
    order by bs.backup_start_date desc

Because the backup stores all active pages from all data files and all log files, you can get a good idea of how large your database is becoming to take appropriate action. If you correlate this with the information in dbo.backupfile, you can get a detailed look at this, file by file. Snapshots can be taken daily and saved to a table to create a trending report over time. Those trends can then be used for everything from predicting space requirements (for both the backups and the databases themselves) to predicting the time required to restore the database and much more.

One more common use: listing the files needed for a restore for a given database on a given day. This is a very quick example, but the query can be modified to create a script that will restore the database to a given state, thus reducing the time required to recover from an emergency.

select bs.database_name, cast(bs.backup_start_date as date) as backup_date, bmf.physical_device_name, bf.file_type, bf.physical_name
    from dbo.backupset bs
        inner join dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id
        inner join dbo.backupfile bf on bf.backup_set_id = bs.backup_set_id

There’s no more important duty for a DBA than to make sure that your backups can be used to restore your databases in an emergency. It’s worth the time to double- and triple-check your backups’ success and find out all you can about them. Explore these tables, and enjoy the results!