Backups Snapshot

SET NOCOUNT ON;

DECLARE @RunDate DATETIME;
SET @RunDate = GETDATE();

-- RETRIEVE BACKUP INFO --
WITH BackupInfo (backup_start_date, backup_finish_date, backup_type, backup_size, database_name
, has_backup_checksums, is_damaged, compressed_backup_size, logical_device_name
, physical_device_name, device_type)
AS (
SELECTbs.backup_start_date
, bs.backup_finish_date
, CASE bs.type
WHEN 'D' THEN 'Full backup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File/Filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
WHEN NULL THEN 'No backups'
ELSE 'Unknown (' + bs.[type] + ')'
END
, bs.backup_size
, bs.database_name
, bs.has_backup_checksums
, bs.is_damaged
, bs.compressed_backup_size
, bmf.logical_device_name
, bmf.physical_device_name
, CASE WHEN bmf.device_type IN (2, 102) THEN 'DISK'
WHEN bmf.device_type IN (5, 105) THEN 'TAPE'
END
FROMmsdb..backupset bs
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
)

-- REPORT ON DATABASE / BACKUP INFO --
SELECT[DatabaseId] = d.database_id
, [DatabaseName] = d.name
, [SQLVersion] = CASE d.[compatibility_level]
WHEN 70 THEN '7'
WHEN 80 THEN '2000'
WHEN 90 THEN '2005'
WHEN 100 THEN '2008'
WHEN NULL THEN 'OFFLINE'
END
, [RecoveryModel] = d.recovery_model_desc
, [DatabaseState] = d.state_desc
, [RecoveryState] = CASE d.state
WHEN 0 THEN 'N/A'
ELSE CASE d.is_cleanly_shutdown
WHEN 1 THEN 'NO RECOVERY'
WHEN 0 THEN 'RECOVERY'
END
END
, [BackupsLast30Days] = mx.backup_last_30
, [MostRecentBackup] = bak.backup_start_date
, [MostRecentType] = bak.backup_type
, [MostRecentSize_MB] = CONVERT(INT, bak.compressed_backup_size / 1024 /*KB*/ / 1024 /*MB*/)
, [CompressionRatio] = CONVERT(NUMERIC(4,1), (1 - (bak.compressed_backup_size * 1.0 / NULLIF(bak.backup_size, 0))) * 100)
, [Last30AvgSize_MB] = CONVERT(INT, mx.backup_avg_size / 1024 /*KB*/ / 1024 /*MB*/)
, [MostRecentDuration_sec] = DATEDIFF(SS, bak.backup_start_date, bak.backup_finish_date)
, [Last30AvgDuration_sec] = mx.backup_avg_duration
, [MostRecentLogicalDevice] = bak.logical_device_name
, [MostRecentPhysicalDevice] = bak.physical_device_name
, [MostRecentDeviceType] = bak.device_type
, [UsedCHECKSUM] = bak.has_backup_checksums
, [BackupDamaged] = bak.is_damaged
, [LogBackupCheck] = CASE WHEN d.recovery_model = 3 /*SIMPLE*/ THEN 0 ELSE 1 END
FROMsys.databases d
LEFT OUTER JOIN (
SELECTdatabase_id = DB_ID(database_name)
, backup_type
, backup_start_date = MAX(backup_start_date)
, backup_last_30 = SUM(CASE WHEN backup_start_date BETWEEN DATEADD(DD, -30, @RunDate) AND @RunDate THEN 1 ELSE 0 END)
, backup_avg_duration = AVG(DATEDIFF(SS, backup_start_date, backup_finish_date))
, backup_avg_size = AVG(compressed_backup_size)
FROMBackupInfo
GROUP BY DB_ID(database_name)
, backup_type
) mx ON d.database_id = mx.database_id
LEFT OUTER JOIN BackupInfo bak
ON mx.database_id = DB_ID(bak.database_name)
AND mx.backup_start_date = bak.backup_start_date
ORDER BY d.name
, mx.backup_start_date;