Full Database Backup status report via email alert in SQL Server

 Set nocount ON

Declare @DBName Varchar (500), @dbIs SQL_Variant, @dbStatus SQL_Variant,@replicaid Varchar(500),@cmd Varchar (1000),@profile_name Varchar(1000)

If  OBJECT_ID('tempdb..#DBBackup') is not NULL

     Drop table tempdb..#DBBackup

If  OBJECT_ID('tempdb..#Log') is not NULL

     Drop table tempdb..#Log

     

Create Table #DBBackup (Server varchar(500),DB varchar(1000), Backup_Status varchar(1000))

Create Table #Log (Server varchar(500),DB varchar(1000),Last_Backup Date,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)

DECLARE @xml NVARCHAR(MAX)

DECLARE @body NVARCHAR(MAX)

Declare cur CURSOR for Select Name from sys.databases where name not in ('tempdb') and state_desc='ONLINE' order by Name

Open cur

fetch next from cur into @DBName

while @@fetch_status=0

     Begin

Select @dbIs=Databasepropertyex (@DBName,'Updateability'),@dbStatus=Databasepropertyex (@DBName,'Status')

Select @replicaid=replica_id from sys.databases where name=@DBName

If   @replicaid is NULL And @dbStatus='ONLINE' 

       Begin

    insert into #DBBackup Select Upper(@@SERVERNAME) Server, Upper(@DBName) Database, ''

       End  

  Else If (@dbIs='READ_WRITE' or @dbIs IS NULL ) And @dbStatus='ONLINE' AND @replicaid is Not NULL

         Begin

   insert into #DBBackup Select Upper(@@SERVERNAME) Server, Upper(@DBName) Database, ''

         End

   fetch next from cur into @DBName

      End

Close Cur

Deallocate Cur

insert into #Log

SELECT @@Servername 'Server', Database_Name, 

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup, 

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size1048576) AS Backup_Size ,(Compressed_Backup_Size1048576) AS Compressed_Backup_Size

FROM MSDB.dbo.BackupSet

WHERE Type = 'd' 

GROUP BY Database_Name,backup_size,compressed_backup_size

Having  DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) 120

order by 2 

Update #DBBackup

Set Backup_Status='FAILED'

where  DB not in (Select DB from #Log)

Update #DBBackup

Set Backup_Status='Completed'

where Backup_Status'FAILED'

SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',L.Last_Backup as 'td','',L.Backup_Size as 'td',

'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join  #Log L on DB.DB=L.DB

                    FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='htmlbodyH3Full Database Backup status H3

table border = 1 

tr

th Server Name th th Database Name th th Backup Status th th Last_Backup th th Backup_Size (MB) th th Compressed_Backup_Size (MB) th tr'    

 

SET @body = @body + @xml +'tablebodyhtml'

Set @cmd= @@SERVERNAME + ' Full Database Backup '

SELECT Top 1 @profile_name=name  FROM msdb.dbo.sysmail_profile

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @profile_name, -- replace with your SQL Database Mail Profile 

@body = @body,

@body_format ='HTML',

@recipients = 'itesadba@sqldbanow.com', -- replace with your email address

@subject = @cmd

DROP TABLE [#DBBackup]

DROP TABLE [#Log]