THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 t1.name AS 'DatabaseName'

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

,t3.user_name AS 'UserResponsible'

,t3.name AS backup_name

,t3.description

,t3.backup_start_date

,t3.backup_finish_date

,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'

,t6.physical_device_name

,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'

,t3.recovery_model 

FROM sys.databases t1

INNER JOIN backupset t3

ON (t3.database_name = t1.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.

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement