-- http://www.sqlserver-training.com/how-to-monitor-sql-server-backups/- -- http://www.codeproject.com/Articles/105573/Making-the-best-use-out-of-Mainenance-Plans-on-SQL SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, bs.database_name, bs.backup_start_date, bs.backup_finish_date, bs.expiration_date, CASE bs.type WHEN 'D' THEN 'Full Database' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Differential' WHEN 'F' THEN 'File Level' WHEN 'G' THEN 'File Level Differential' WHEN 'P' THEN 'Partial' WHEN 'Q' THEN 'Differential partial' END AS backup_type, convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as 'Backup Size in MB', convert(varchar,cast(bs.compressed_backup_size /1024/1024 as money),10) as 'comp Size in MB', bmf.logical_device_name, bmf.physical_device_name, bs.name AS backupset_name, bs.description FROM msdb.dbo.backupmediafamily bmf INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 10) and bs.type = 'D' ORDER BY bs.database_name, bs.backup_finish_date
