365DBA Blog

Database Stuff

SQL Server Monitoring Scripts - 4 - Missing Transaction Log Backups

We don't want any databases in full recovery going without their log backups so here is a script to let us know when we have one. You will need to adjust the @Threshold variable to your specific needs (which is true for all of the scripts in this series) and then you are set. This script takes into account whether the database is mirrored, it's status, and if it's a system database.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Log Backup Check---------------'
Declare @MissingLogBackups Table(ID Int Identity, DatabaseName VarChar(100))
Declare @Threshold Int
Select @Threshold = 4
Insert Into @MissingLogBackups (DatabaseName)
Select d.[name] From sys.databases d 
Left Join msdb.dbo.backupset b On d.[name] = b.database_name
And b.[type] = 'L' 
Inner Join sys.database_mirroring m On d.[database_id] = m.[database_id]
Where DatabasePropertyEx(d.[name], 'Recovery') <> 'SIMPLE'
and DatabasePropertyEx(d.[name], 'Status') = 'ONLINE'
And d.[name] Not In ('master', 'msdb', 'tempdb', 'model')
And ((m.mirroring_guid Is Null)
Or (m.mirroring_guid Is Not Null And mirroring_role = 1))
Group By d.[name]
Having IsNull(Max(b.backup_finish_date), '1/1/2000') < DateAdd(hh, (-1 * @Threshold), GetDate())
If (Select Count(*) From @MissingLogBackups) > 0
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @MissingLogBackups
Select @Counter = Min(ID) From @MissingLogBackups
While @Counter <= @Count
Select 'Server: ' + Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + 
'. Missing log backups for DB: ' + DatabaseName From @MissingLogBackups Where ID = @Counter
Set @Counter = @Counter + 1