Hi All,
Please guide me how to combine below two scripts using UNION operator.
Script 1:
=========
SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
M.Name As Database_Name,
K.Backup_Start_Date,
K.Backup_Finish_Date,
Backup_Type = CASE Upper(K.[Type])
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential'
END,
Note = CASE
WHEN max(k.backup_start_date) < GETDATE() - 7
THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
--WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/
WHEN ISNULL(max(k.backup_start_date),0) = 0
THEN 'ALERT'
ELSE '-----'
END
FROM Sys.SysDatabases M (NOLOCK)
INNER JOIN Sys.SysAltFiles SF (NOLOCK)
ON M.[DBId] = SF.[DBId]
LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
ON M.Name = K.Database_Name And K.[Type] = 'D'
AND K.Backup_Start_Date >= getdate()-7
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
ORDER BY K.[Type] DESC
Script 2:
===========
SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
M.Name As Database_Name,
K.Backup_Start_Date,
K.Backup_Finish_Date,
Backup_Type = CASE Upper(K.[Type])
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential'
END,
Note = CASE
WHEN max(k.backup_start_date) < GETDATE() - 2
THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
--WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/
WHEN ISNULL(max(k.backup_start_date),0) = 0
THEN 'ALERT'
ELSE '-----'
END
FROM Sys.SysDatabases M (NOLOCK)
INNER JOIN Sys.SysAltFiles SF (NOLOCK)
ON M.[DBId] = SF.[DBId]
LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
ON M.Name = K.Database_Name And K.[Type] = 'I'
AND K.Backup_Start_Date >= getdate()-2
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
ORDER BY K.[Type] DESC
SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinenam
M.Name As Database_Name,
K.Backup_Start_Date,
K.Backup_Finish_Date,
Backup_Type = CASE Upper(K.[Type])
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential'
END,
Note = CASE
WHEN max(k.backup_start_date) < GETDATE() - 7
THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
WHEN max(k.backup_start_date) < GETDATE() - 2
THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
--WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/
WHEN ISNULL(max(k.backup_start_
THEN 'ALERT'
ELSE '-----'
END
FROM Sys.SysDatabases M (NOLOCK)
INNER JOIN Sys.SysAltFiles SF (NOLOCK)
ON M.[DBId] = SF.[DBId]
LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
ON M.Name = K.Database_Name And ((K.[Type] = 'I'
AND K.Backup_Start_Date >= getdate()-2) OR (K.[Type] = 'D'
AND K.Backup_Start_Date >= getdate()-7))
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
ORDER BY K.[Type] DESC