THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

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.



AaronBertrand said:

IMHO you should always use schema prefixes when identifying objects, so in the context of msdb it should be:

   INNER JOIN dbo.backupset t3


In this specific case, since we know these will always be in msdb, even better (and to avoid the "you need to be in the MSDB database for this to run" disclaimer) would be:

   INNER JOIN msdb.dbo.backupset t3


Just a suggestion.  :-)

March 30, 2010 1:22 PM

BuckWoody said:

GREAT suggestion, Aaron - thanks for that catch!

March 30, 2010 2:22 PM

Holger Schmeling said:

I had a lot of fun reading this. No sorry, not the script. It's the disclaimer I liked most!

March 31, 2010 8:56 AM
New Comments to this post are disabled

About BuckWoody

This Blog


Privacy Statement