View: dbo.Backup_History_View | |||
View definition | |||
create view dbo.Backup_History_View as select * , datediff(minute, backup_finish_date, getdate()) as backup_age_minutes from ( select B.media_set_id -- unique key for query result , B.database_name , convert(varchar(1), B.type) as type , B.backup_start_date , B.backup_finish_date , B.server_name , B.machine_name , B.user_name , convert(decimal(20,0), B.backup_size) as backup_size , isnull(F.logical_device_name, F.physical_device_name) as backup_location from msdb.dbo.backupset AS B left outer join msdb.dbo.backupmediafamily AS F on F.media_set_id = B.media_set_id and F.family_sequence_number = B.first_family_number where B.type in ('D', 'I') and -- the backup is completed B.backup_finish_date is not null and -- exclude backups created when the clock was set ahead of the current time B.backup_finish_date <= getdate() and B.database_name = db_name() ) as X | |||