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 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


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' 


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


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


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


   fetch next from cur into @DBName


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 


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



No comments:

Post a Comment