Find the last full backup date for all databases on a server

Consider a situation in which you're taking over a number of SQL Servers, and you find yourself wondering about the backup situation - maybe you need to pull a full backup history for each database on each server, or maybe you just need to verify that your databases are being backed up.  This is a quick script that pulls the last full backup date for each database on the server - I'm including a SQL Server 2000-compatible version of the script:

SQL Server 2000

select d.[name],
    x.backup_date
from sysdatabases d
    left outer join
    (
        select database_name,
            convert(varchar(50),max(backup_finish_date),110) as backup_date
        from msdb..backupset
        where [type] = 'D'
        group by database_name
    ) x on x.database_name = d.[name]
order by d.[name]

SQL Server 2008 (should also work for SQL Server 2005)

;with x as
(
    select database_name,
        backup_size                                 as backup_size_bytes,
        backup_size / 1024                          as backup_size_kbytes,
        backup_size / (1024*1024)                   as backup_size_mbytes,
        compressed_backup_size,
        convert(varchar(50),backup_finish_date,110) as backup_date,
        [type],
        row_number() over ( partition by database_name order by backup_finish_date desc ) as rnk
    from msdb..backupset
    where [type] = 'D'       
)

select d.[name],
    d.recovery_model_desc,
    x.*
from sys.databases d
    left outer join x on x.database_name = d.[name]
                        and x.rnk = 1
order by d.[name]

Note that this is only the first step in your due diligence - you still have to determine where your databases are being backed up to, your retention window (locally and on any off-site tapes or servers), and (of course) run periodic fire drills to make sure your backup solution is adequate to the task - more on that in a later post.