EXEC..Backup_Report
@MailProfile = 'ABC' ,
@MailID = 'abc@abc.com',
@Server = 'ABC'
--keep sp and run job 9am everyday
USE [master]
GO
/****** Object: StoredProcedure [dbo].[Backup_Report] Script Date: 3/1/2022 4:18:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Backup_Report] (
@MailProfile NVARCHAR(200),
@MailID NVARCHAR(2000),
@Server VARCHAR(100) = NULL)
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;
DECLARE @ServerName VARCHAR(100);
SET @ServerName = ISNULL(@Server,@@SERVERNAME);
CREATE TABLE #Backup_Report(
ServerName VARCHAR(300),
Database_name varchar(300),
BackupType varchar(50),
NotBackedUpSince varchar(100),
LastBackupDate VARCHAR(50),
Backuppath nvarchar(1000),
NotBackedUpSince_hours varchar(50));
INSERT INTO #Backup_Report
SELECT @@servername as ServerName,B.name as Database_Name ,
case when A.type = 'D' then 'Full'
when A.type = 'L' then 'Log'
When A.type ='I' then 'Diff'
end as BackupType ,
case
when A.type = 'D' then
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Days'
when A.type = 'I' then
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Days'
when A.type = 'L' then
ISNULL(STR(ABS(DATEDIFF(minute, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Minutes'
end as NotBackedUpSince
,
ISNULL(Convert(char(20), MAX(backup_finish_date), 120), 'NEVER') as LastBackupDate
,
max(convert(char(100),physical_device_name)) as BackupPath ,
-- case when A.type = 'D' then 'Full'
--when A.type = 'L' then 'Log'
--When A.type ='I' then 'Diff'
--end as BackupType ,
case
when A.type = 'D' then
ISNULL(STR(ABS(DATEDIFF(hour, GetDate(),MAX(Backup_finish_date)))), 'NEVER')
when A.type = 'I' then
ISNULL(STR(ABS(DATEDIFF(Hour, GetDate(),MAX(Backup_finish_date)))), 'NEVER')
when A.type = 'L' then
ISNULL(STR(ABS(DATEDIFF(minute, GetDate(),MAX(Backup_finish_date)))), 'NEVER')
end as NotBackedUpSince_hours
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name
left outer join msdb..backupmediafamily C
on C.media_set_id = A.media_set_id
where B.name not in ('tempdb')
--and A.type ='D'
GROUP BY B.Name,A.type order by 1
/*************************************************************/
/****************** HTML Preparation *************************/
/*************************************************************/
DECLARE @TableHTML VARCHAR(MAX),
@StrSubject VARCHAR(100),
@Oriserver VARCHAR(100),
@Version VARCHAR(250),
@Edition VARCHAR(100),
@ISClustered VARCHAR(100),
@SP VARCHAR(100),
@ServerCollation VARCHAR(100),
@SingleUser VARCHAR(5),
@LicenseType VARCHAR(100),
@Cnt int,
@URL varchar(1000),
@Str varchar(1000),
@NoofCriErrors varchar(3)
-- Variable Assignment
SELECT @Version = @@version
SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))
SET @Cnt = 0
IF serverproperty('IsClustered') = 0
BEGIN
SELECT @ISClustered = 'No'
END
ELSE
BEGIN
SELECT @ISClustered = 'YES'
END
SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername'))
SELECT @strSubject = 'Backup Report ('+ CONVERT(VARCHAR(100), @SERVERNAME) + ')'
SET @TableHTML =
'<font face="Verdana" size="4">Backup Report</font>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">
<tr>
<td width="39%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
</tr>
<tr>
<td width="39%" height="27"><font face="Verdana" size="2">' + @ServerName +'</font></td>
</tr>
</table>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">
<tr>
</table>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
</tr>'
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Backup Report</font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">
<tr>
<th align="left" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">ServerName</font></th>
<th align="left" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database_Name</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">BackupType</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">NotBackedupsince</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">lastBackupDate</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">backuppath</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), ServerName), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Database_Name), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), BackupType), '') +'</font></td>' +
CASE WHEN NotBackedUpSince_hours > 170 and backuptype = 'Full' THEN
'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'
WHEN NotBackedUpSince_hours >30 and BackupType = 'Diff' THEN
'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'
WHEN NotBackedUpSince_hours >30 and BackupType = 'Log' THEN
'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'
ELSE
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'
END +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(1000), lastBackupDate), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(1000), backuppath), '') +'</font></td>' +
'</tr>'
FROM
#Backup_Report
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,--'MMStuckup',
@recipients=@MailID, --'uday.arumilli@ge.com',
@subject = @strSubject,
@body = @TableHTML,
@body_format = 'HTML' ;
DROP TABLE #Backup_Report;
SET NOCOUNT OFF;
SET ARITHABORT OFF;
END
No comments:
Post a Comment