Set nocount ON
Declare @DBName Varchar (500), @dbIs SQL_Variant, @dbStatus SQL_Variant,@replicaid Varchar(500),@cmd Varchar (1000),@profile_name Varchar(1000),@Bkupstatus Varchar (500)
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 Datetime,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' and recovery_model_desc='FULL' 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_Size/1048576) AS Backup_Size ,(Compressed_Backup_Size/1048576) AS Compressed_Backup_Size
FROM MSDB.dbo.BackupSet
WHERE Type = 'l'
GROUP BY Database_Name,backup_size,compressed_backup_size
Having DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) <5
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
--SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',convert(varchar(20),L.Last_Backup,120) 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 where DB.Backup_Status='FAILED'
Select @Bkupstatus=Backup_Status from #DBBackup
If @Bkupstatus='FAILED'
Begin
SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',convert(varchar(20),L.Last_Backup,120) 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 where DB.Backup_Status='FAILED'
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Log 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>'
--<td bgcolor=#F0E68C><b>FreeSpace(%)<b> </td> <th>Capture_Date</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
Set @cmd= @@SERVERNAME + ': Log 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 = 'IT.ESA-DBA@sqldbanow.com', -- replace with your email address
@subject = @cmd
End
DROP TABLE [#DBBackup]
DROP TABLE [#Log]
No comments:
Post a Comment