Easy Backup History Review Script

I often use these scripts to check on the backups of all databases in an unfamiliar SQL Server instance, regardless of the platform that is taking the backups (SQL maintenance plans, T-SQL scripts in an agent job, and third-party software).

The query sorts databases by the backup type and then the recovery model configured. Because the backups are grouped by database name, it shows the most recent full backup for each database, most recent differential if it exists, and then the most recent transaction log backup (but only for databases in FULL or BULK_LOGGED recovery models).

Most importantly, it will show the most recent transaction log backup for all databases in FULL or BULK_LOGGED recovery models, even if a transaction log backup has never been taken.

If any of the dates look out of place, too old or NULL, they will stand out right away.

• The first two scripts are functionally equivalent. The first uses SQL 2005 system views (sys.databases) that are only appropriate if all databases are running SQL 2005 compatibility mode or higher. 
• The second script is using SQL 2000 system views (sysdatabases), which are now deprecated.
• The third script provides a granular list of all backup events by database, and is valid for SQL 2000 and up.

--sql2005 and above
select 
 database_Name
 , backuptype 
 , d.recovery_model_desc
 , BackupDate = MAX(BackupDate)
 from sys.databases d
 inner join 
 (
select distinct 
 database_name
 , backuptype = case type WHEN 'D' then 'Database'
       WHEN 'I' then 'Differential database'
       WHEN 'L' then 'Transaction Log'
       WHEN 'F' then 'File or filegroup'
       WHEN 'G' then 'Differential file'
       WHEN 'P' then 'Partial'
       WHEN 'Q' then 'Differential partial' END
 , BackupDate = MAX(backup_start_date)  
 from msdb.dbo.backupset bs       
 group by Database_name, type
 UNION 
 select distinct
 db_name(d.database_id)
 , backuptype = 'Database'
 , null
 FROM master.sys.databases d
 UNION
 select distinct
 db_name(d.database_id)
 , backuptype = 'Transaction Log'
 , null
  FROM master.sys.databases d
  where d.recovery_model_desc in ('FULL', 'BULK_LOGGED')
  
 ) a
 on db_name(d.database_id) = a.database_name
 group by database_name, backuptype, d.recovery_model_desc
order by backuptype, recovery_model_desc, BackupDate asc
 
 

 --sql 2000 and above
select distinct 
 database_name = d.name 
 , a.backuptype 
 , RecoveryModel = databasepropertyex(d.name, 'Recovery')  
 , BackupDate = Max(a.backup_start_date)  
 from master.dbo.sysdatabases d
 left outer join 
 (  select distinct 
   database_name
   , backuptype = case type WHEN 'D' then 'Database'
         WHEN 'I' then 'Differential database'
         WHEN 'L' then 'Transaction Log'
         WHEN 'F' then 'File or filegroup'
         WHEN 'G' then 'Differential file'
         WHEN 'P' then 'Partial'
         WHEN 'Q' then 'Differential partial' END
   , backup_start_date = MAX(backup_start_date)   
   from msdb.dbo.backupset bs       
   group by Database_name, type
   UNION 
   select distinct
     d.name
   , backuptype = 'Database'
   , null
   FROM master.dbo.sysdatabases d
   UNION
   select distinct
     d.name
   , backuptype = 'Transaction Log'
   , null
    FROM master.dbo.sysdatabases d
    where databasepropertyex(d.name, 'Recovery') in ('FULL', 'BULK_LOGGED')
  
 ) a
 on d.name = a.database_name
 group by d.name , backuptype , databasepropertyex(d.name, 'Recovery')
order by backuptype, RecoveryModel, BackupDate asc
 

--granular backup history
select distinct 
 database_name
 , type
 , backuptype = case type WHEN 'D' then 'Database'
       WHEN 'I' then 'Differential database'
       WHEN 'L' then 'Transaction Log'
       WHEN 'F' then 'File or filegroup'
       WHEN 'G' then 'Differential file'
       WHEN 'P' then 'Partial'
       WHEN 'Q' then 'Differential partial' END
 , BackupDate = backup_start_date
 , database_backup_lsn
 , bf.physical_device_name
 from msdb.dbo.backupset bs 
 left outer join msdb.dbo.[backupmediafamily] bf
 on bs.[media_set_id] = bf.[media_set_id]
 --where database_name = 'databasenamehere'
 --and type in ('d', 'i')
 
  order by database_name asc, backupdate desc