365DBA Blog

Database Stuff

SQL Server Monitoring Scripts - 6 - Missing Differential Backups

And to round out the missing backups, we need to add the check for missing differentials (if your backup strategy includes differential backups). As with the other related scripts, the threshold is in hours so you can adjust as necessary. 

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Differential Backup Check---------------'
Declare @MissingBackups Table(ID Int Identity, DatabaseName VarChar(100))
Declare @Threshold Int
Select @Threshold = 28 --hours
Insert Into @MissingBackups (DatabaseName)
Select d.[name] From sys.databases d 
Left Join msdb.dbo.backupset b On d.[name] = b.database_name
And b.[type] = 'I' 
Inner Join sys.database_mirroring m On d.[database_id] = m.[database_id]
Where DatabasePropertyEx(d.[name], 'Updateability') = 'READ_WRITE'  
And DatabasePropertyEx(d.[name], 'Status') = 'ONLINE'
And d.[name] Not In ('tempdb')
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 @MissingBackups) > 0
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @MissingBackups
Select @Counter = Min(ID) From @MissingBackups
While @Counter <= @Count
Select 'Server: ' + Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + 
'. Missing differential backups for DB: ' + DatabaseName From @MissingBackups Where ID = @Counter
Set @Counter = @Counter + 1