Alert – Databases do not have Transaction log backups

The SP below sends alert when there are no transaction log backups in the last 90 minutes.

CREATE PROCEDURE [dbo].[usp_NoTlogBackup] (@Length INT=90) -- 90 Minutes
AS
DECLARE @BadDatabases VARCHAR(8000)
DECLARE @Body VARCHAR(8000)

SELECT @BadDatabases = STUFF(
(select ', ' + cast(a.database_name as varchar(100))
 from 
 (
 select [name] as database_name
 from master.dbo.sysdatabases
 where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') 
 and databasepropertyex([name], 'isinstandby') = 0
 and databasepropertyex([name], 'status') = 'online'
 and databasepropertyex([name], 'updateability') = 'read_write'
 and [name] not in ('model','tempdb')
 ) a
 left join msdb.dbo.backupset b
 on a.database_name = b.database_name 
 and b.type='L' 
 and datediff(hour,b.backup_finish_date,getdate()) < @Length
 where b.database_name is null
FOR XML PATH ('')
),1,2,'')

IF (@BadDatabases IS NOT NULL)
BEGIN

declare @ServerT varchar(100)
select @ServerT = 'Alert Backup - '+@@SERVERNAME

SET @Body = 'On ' + @@SERVERNAME + ' The following Databases do not have Transaction log backups: ' + @BadDatabases
PRINT @Body

 EXEC msdb . dbo. sp_send_dbmail        
      @recipients=N'mail@mail.com;' ,
      @body = @Body ,         
      @subject = @ServerT,         
      @profile_name ='profile_name',         
      @body_format = 'HTML'

END
Advertisements

Danilo Braga is a Database Administrator with over nine years of experience. He is a Microsoft Certified IT Professional (MCITP) Database Administrator and Developer. His experience includes solutions for management, assessment and implementation of SQL Server environments making use of best practices, performance tuning, and high availability.

Tagged with: ,
Posted in Alerts, Backup, Scripts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: