You may want to consider putting an ELSE in every CASE statement. If this script is used with a compatability_level of something other than 80, 90, 100, 110, it will return a NULL. Same if Microsoft introduces a new backup_type.
A number of calculated columns would also be useful:
[Backup Duration(seconds)] = DATEDIFF(second, [backup_start_time], [backup_end_time])
[Thruput (bytes per second)] = COALESCE([compressed_backup_size], [backup_size]) / DATEDIFF(second, [backup_start_time], [backup_end_time])
[Compression Ratio] = CASE WHEN ISNULL([compressed_backup_size], 0) > 0 THEN [compressed_backup_size] / [backup_size] END
You may also want to include data from msdb.dbo.backupfile so you can capture statistics about the size of the mdf, ndf and ldf files being backed up. This can be used to calculate the ratio between the size of the file being backed up and the size of the backup file.
You have to remember that data will age out of msdb.dbo.backupset if you have scheduled sp_delete_backuphistory in order to keep the size of the msdb database manageable. It may be worth it to store the data derived from this query into a separate table. This can be very useful for tracking the performance of the backup subsystem. It can also be useful for storage forcecasting as you'll have file space statistics for both the database file size and the backup file size.
commented on Jan 7 2012 12:26PM