365DBA Blog

Database Stuff

SQL Server Monitoring Scripts - 5 - Missing Full Backups

We also want to know about any databases that do not have a full backup; maybe they're new or maybe the backup failed for some reason and we weren't notified, whatever the case, it definitely deserves some investigating.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Full Backup Check---------------'
Declare @MissingBackups Table(ID Int Identity, DatabaseName VarChar(100))
Declare @Threshold Int
 --This threshold is in hours so you can specify the total hours or 24 * number of days in your full backup cycle
Select @Threshold = (24 * 7)
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] = 'D' 
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 full backups for DB: ' + DatabaseName From @MissingBackups Where ID = @Counter
Set @Counter = @Counter + 1