BackupHIstory.sql

USE [msdb]
GO

CREATE view [dbo].[vwBackupHistory]
as

select
	 this_instance_name = @@SERVERNAME
	,source_server_name = left(bs.server_name, 40)
	,database_name = left(bs.database_name, 60)

	,bs.type
	,physical_device_name = left(bmf.physical_device_name, 120)
	
	,bs.backup_start_date 
	,bs.backup_finish_date
	,bs.backup_size
	,backup_user_name = bs.user_name
from	msdb..backupset bs
	left outer join msdb.dbo.backupmediafamily bmf
		on bmf.media_set_id = bs.media_set_id 
	left outer join msdb.dbo.restorehistory rh
			on bs.backup_set_id = rh.backup_set_id
where
	rh.backup_set_id is null
GO