Backup Meta-Data

I'm working on a PowerShell script to show me the trending durations of my backup activities. The first thing I need is the data, so I looked at the Standard Reports in SQL Server Management Studio, and found a report that suited my needs, so I pulled out the script that it runs and modified it to this T-SQL Script.

A few words here - you need to be in the MSDB database for this to run, and you can add a WHERE clause to limit to a database, timeframe, type of backup, whatever. For that matter, I won't use all of the data in this query in my PowerShell script, but it gives me lots of avenues to graph:

SELECT distinct AS 'DatabaseName'

,(datediff( ss,  t3.backup_start_date, t3.backup_finish_date)) AS 'DurationInSeconds'

,t3.user_name AS 'UserResponsible'

, AS backup_name




,CASE WHEN t3.type = 'D' THEN 'Database'

WHEN t3.type = 'L' THEN 'Log'

WHEN t3.type = 'F' THEN 'FileOrFilegroup'

WHEN t3.type = 'G' THEN 'DifferentialFile'

WHEN t3.type = 'P' THEN 'Partial'

WHEN t3.type = 'Q' THEN 'DifferentialPartial'

END AS 'BackupType'

,t3.backup_size AS 'BackupSizeKB'


,CASE WHEN t6.device_type = 2 THEN 'Disk'

WHEN t6.device_type = 102 THEN 'Disk'

WHEN t6.device_type = 5 THEN 'Tape'

WHEN t6.device_type = 105 THEN 'Tape'

END AS 'DeviceType'


FROM sys.databases t1

INNER JOIN backupset t3

ON (t3.database_name = ) 

LEFT OUTER JOIN backupmediaset t5

ON ( t3.media_set_id = t5.media_set_id )

LEFT OUTER JOIN backupmediafamily t6

ON ( t6.media_set_id = t5.media_set_id )

ORDER BY backup_start_date DESC

I'll munge this into my Excel PowerShell chart script tomorrow.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Comments (4)
  1. mbourgon says:

    Did something similar using the system tables, where I use them to see what backups we actually have on disk, as opposed to what we _should_ have.  (see the linked URL – basically, gets a directory list, then scans it using xp_cmdshell, and compares the two)  

    Another use: size trending.  The database size (pre-compression, IIRC) is in there, which means you could query back quite a while and show how much each database is growing.  Useful for capacity planning.  

    Also, you could use them for documentation, since you have a listing for where every database backup resides.

    There’s a plethora of uses for these tables.

  2. Alan Craig says:

    Love the Disclaimer. May I use it on my blogs? Proper credit will be given.

  3. BuckWoody says:

    Alan – feel free!

Comments are closed.

Skip to main content