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]
No comments:
Post a Comment