Set nocount ON
Declare @DBName Varchar (500), @dbIs SQL_Variant, @dbStatus SQL_Variant,@replicaid Varchar(500)
Declare @cmd Varchar (1000),@profile_name Varchar(1000), @description Varchar (500)
If OBJECT_ID('tempdb..#DBBackup') is not NULL
Drop table tempdb..#DBBackup
If OBJECT_ID('tempdb..#DBBackup') is not NULL
Drop table tempdb..#FullBackup
If OBJECT_ID('tempdb..#DBBackup') is not NULL
Drop table tempdb..#DiffBackup
If OBJECT_ID('tempdb..#Log') is not NULL
Drop table tempdb..#Log
Create Table #DBBackup (Server varchar(500),DB varchar(1000),Full_Backup_Status varchar(1000),Diff_Backup_Status varchar(1000), Log_Backup_Status varchar(1000))
Create Table #FullBackup (Server varchar(500),DB varchar(1000),Full_Last_Backup Datetime,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)
Create Table #DiffBackup (Server varchar(500),DB varchar(1000),Diff_Last_Backup Datetime,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)
Create Table #Log (Server varchar(500),DB varchar(1000),Log_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
Set @description= ''
SELECT @description = hars.role_desc
FROM sys.DATABASES d
INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id
WHERE database_id = DB_ID(@DBName)
if (@description<>'SECONDARY' and @dbIs='READ_WRITE')
Begin
insert into #DBBackup Select Upper(@@SERVERNAME) "Server", Upper(@DBName) "Database", '','',''
End
fetch next from cur into @DBName
End
Close Cur
Deallocate Cur
--Full backup Status--
If Datename(weekday,(getdate()))='Monday'
Begin
insert into #FullBackup
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 = 'd' and is_copy_only=0
GROUP BY Database_Name,backup_size,compressed_backup_size
Having DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) <360
order by 2
End
else
Begin
insert into #FullBackup
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 = 'd' and is_copy_only=0
GROUP BY Database_Name,backup_size,compressed_backup_size
Having DATEDIFF(Day, MAX(Backup_Finish_Date), Getdate()) <8
order by 2
End
Update #DBBackup
Set Full_Backup_Status='**Need to backup verify**'
where DB not in (Select DB from #FullBackup)
Update #DBBackup
Set Full_Backup_Status='Completed'
where Full_Backup_Status<>'**Need to backup verify**'
--Select * from #DBBackup
--Diff Backup Satatus -----
If Datename(weekday,(getdate()))='Monday'
Begin
insert into #DiffBackup
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 = 'I'
GROUP BY Database_Name,backup_size,compressed_backup_size
Having DATEDIFF(DAY, MAX(Backup_Finish_Date), Getdate()) <3
order by 2
End
Else
Begin
insert into #DiffBackup
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 = 'I'
GROUP BY Database_Name,backup_size,compressed_backup_size
Having DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) <180
order by 2
End
Update #DBBackup
Set Diff_Backup_Status='**Need to backup verify**'
where DB not in (Select DB from #DiffBackup)
Update #DBBackup
Set Diff_Backup_Status='Completed'
where Diff_Backup_Status<>'**Need to backup verify**'
SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Full_Backup_Status as 'td' ,'',convert(varchar(20),F.Full_Last_Backup,120) as 'td','',
Diff_Backup_Status as 'td','',convert(varchar(20),D.Diff_Last_Backup,120) as td
from #DBBackup DB Left Outer join #FullBackup F on DB.DB=F.DB Left outer join #DiffBackup D on DB.DB=D.DB
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3> Database Backup status </H3>
<table border = 1>
<tr>
<th> Server Name </th> <th> Database Name </th> <th> Full_Backup_Status </th> <th> Full_Last_Backup </th><th> Differential_Backup_Status </th><th> Differential_Last_Backup</th> </tr>'
--<td bgcolor=#F0E68C><b>FreeSpace(%)<b> </td> <th>Capture_Date</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
Set @cmd= @@SERVERNAME + ': Database Backup Status'
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 [#FullBackup]
DROP TABLE [#DiffBackup]
DROP TABLE [#Log]