Monitoring SQL Server Database Backups

This scripts will give you when was the last time database has been backed up.

select    sdb.name as databasename,
        coalesce(convert(varchar(12), max(bus.backup_finish_date), 101),'-') as lastbackuptime
    from
        sys.sysdatabases sdb
left outer join
        msdb.dbo.backupset bus
    on
        bus.database_name = sdb.name

    group by sdb.name



 This Script will give you backup file location of a particular Database
 ** ENTER THE DATABASE NAME **


select  physical_device_name,
        backup_start_date,
        backup_finish_date,
        backup_size/1024.0 as backupsizekb
    from
        msdb.dbo.backupset b
join
        msdb.dbo.backupmediafamily m
    on    b.media_set_id = m.media_set_id
   
where
        database_name = 'yourdb_name'
    order by backup_finish_date desc
   


Check to see if your database has been backed up from the past 2days or not

select
        databasename = d.name,
        latetstbackupdate = isnull(convert(varchar(30),max(b.backup_finish_date),120),'no backup'),
        'days' = min(datediff(hh,b.backup_finish_date,getdate())) / 24,
        'hours' = min(datediff(hh,b.backup_finish_date,getdate())) % 24
from
        master.sys.databases d
    left outer join
        msdb.dbo.backupset b
    on    d.name = b.database_name
where
        d.name not in ('tempdb','model')
    group by d.name
    having isnull(min(datediff(hh,b.backup_finish_date,getdate())),999) >= 24 * 2-- change the 2 when you want to use a different amount of days
    order by d.name