USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vwServerBackup]
as
With backupBase
As
(
Select
sd.name
From msdb..backupmediafamily bmf
INNER JOIN msdb..backupset bms ON bmf.media_set_id = bms.media_set_id
INNER JOIN master..sysdatabases sd ON bms.database_name = sd.name
AND bms.backup_start_date = (SELECT MAX(backup_start_date) FROM [msdb]..[backupset] b2
WHERE bms.database_name = b2.database_name AND b2.type = 'D'
and convert(date,backup_start_date) >= convert(date,getdate()) )
WHERE sd.name NOT IN ('tempdb')
),
DatabaseRef
as
(
Select
Name
From Sysdatabases
WHERE name NOT IN ('tempdb')
),
MissingBackup
as
(
Select
DatabaseName = dt.Name
From DatabaseRef dt
left Join backupBase bk on dt.Name = bk.name
where bk.name is null
),
TotalMissingBackup
as
(
Select
Total = Count(1)
From MissingBackup
)
Select
ServerName = @@SERVERNAME,
BackupIndicatorCategoryName = 'Backup',
BackupIndicatorName = 'Missing',
BackupAVGIndicatorValue = 0,
BackupIndicatorValue = Total,
BackupIndicatorComment = (SELECT DatabaseName + ' ' AS 'data()' FROM MissingBackup FOR XML PATH('')),
CreatedDateOn = GetDate(),
IsOperational = (Case When (datepart(hour, getdate()) Between 8 and 18) Then 1 Else 0 End)
From TotalMissingBackup
GO