Home Forums SQL Server 2008 T-SQL (SS2K8) Send Alert when no log backup completed in 24 hours RE: Send Alert when no log backup completed in 24 hours

  • -- GET DATABASES WITHOUT FULL OR TLOG BACKUPS

    -- dbs with no full backups in last week

    (select cast(a.[name] as varchar(100)) as db_nm into #no_full_backup

    from master.dbo.sysdatabases a

    left join msdb.dbo.backupset b

    on a.[name] = b.database_name and datediff(hour,b.backup_finish_date,getdate())<168 -- 1 week

    and b.type='D'

    where a.[name] != 'tempdb'

    and databasepropertyex(a.[name], 'isinstandby') = 0

    and databasepropertyex(a.[name], 'status') = 'online'

    and databasepropertyex(a.[name], 'updateability') = 'read_write'

    and b.database_name is null )

    -- dbs with no transaction log backup for a day

    (select cast(a.database_name as varchar(100)) as db_nm into #no_tlog_backup

    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())<24

    where b.database_name is null)

    --select * from #no_full_backup

    --select * from #no_tlog_backup

    if (select count(*) from #no_full_backup) > 0

    raiserror ('databases with no full backups in last 7 days exist',16,1) with log

    if (select count(*) from #no_tlog_backup) > 0

    raiserror ('databases with no transaction backups in last 1 days exist',16,1) with log

    drop table #no_full_backup

    drop table #no_tlog_backup

    This one is actually SQL2000 compatible but works on other versions. You would be well to get the database status values from table master.sys.databases

    ---------------------------------------------------------------------