Friday 4 March 2011

Find out when your SQL Server databases were last backed up

Would you like to know when any of your SQL backups were last backed up? Or did you want to verify that a back up actually ran? Why now you can!
select
 max(isnull(datediff(dd,b.backup_start_date,getdate()),0))  as 'Days since backup',
 b.backup_size,
 d.name as database_name
from  
 master..sysdatabases d with (nolock)
left join 
 msdb..backupset b with (nolock)
  on d.name = b.database_name 
   and b.backup_start_date = (select max(backup_start_date) 
    from 
     msdb..backupset b2 
    where
     b.database_name = b2.database_name
     and b2.type = 'D')
where 
 d.name != 'tempdb'
group by
 d.name, b.type, b.backup_size
This produces the following:-