Check last backups for all databases

 

Been way to long between posts. Have had my head down busy with work and being secretary of the cricket club.

 

Here's a handy script from my tool chest. It shows the database status, last full backup and last log backup.

select

    db.[db_name]
    , databasepropertyex(db.[db_name], 'status') as [db_status]
    , db.[last_backup] as [last_db_backup]
    , lg.[last_backup] as [last_log_backup]
    , getdate() as [current_date]
from ( 
    select
        convert(varchar (50), substring(db .name,1,50)) as [db_name]
        , max (bs.backup_finish_date) as [last_backup]
    from master..sysdatabases db (nolock)
        left join msdb..backupset bs ( nolock) 
        on db .name = bs.database_name
        and bs.type = 'd'
    group by db. name
) as db
left join (
    select
        convert (varchar(50), substring (db.name,1 ,50)) as [db_name]
        , max(bs.backup_finish_date ) as [last_backup]
    from master..sysdatabases db (nolock )
        left join msdb ..backupset bs (nolock) 
        on db.name = bs .database_name
        and bs.type = 'l'
    group by db.name
) as lg
on db.[db_name] = lg.[db_name]