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