The most useful script.

There is a newer version below, that displays results in a better way

Plug this in a SQL job and send the results via email and you have a flexible reporting tool: click here for instructions

/*
DESCRIPTION    List the age of the last backup for all databases  for FULL, DIFF and Tlog

Plus databases with no backups ever


CONFIGURATION   none

 
Compatibility list:
MSSQL2005
MSSQL2008

 */



set nocount on
use msdb
 
Select Distinct
    serverproperty('servername')
    , serverproperty('productversion')
    , serverproperty('productlevel')
    ,convert(varchar(30),e.database_name) as DBname
    ,convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery')))
    ,(Select datediff(dd,Max(backup_finish_date), getdate()) From backupset a Where a.database_name=e.database_name --and a.server_name  = @@servername
        and type='D'  Group by a.database_name) Full_Bk
        ,(Select datediff(dd,Max(backup_finish_date), getdate()) From backupset b  Where b.database_name=e.database_name
              --and b.server_name  = @@servername
              and type='L' Group by b.database_name) Log_Bk
       ,(Select datediff(dd,Max(backup_finish_date), getdate())  From backupset c Where c.database_name=e.database_name --and c.server_name  = @@servername
              and type='I' Group by c.database_name) Diff_Bk
From backupset e

Where
    e.database_name Not in ('tempdb','pubs','northwind','model')
    and databasepropertyex(database_name,'Status') = 'online'
    and e.database_name in (Select Distinct name from master..sysdatabases)
    --and e.server_name = select @@Servername
 
-- never backed up
Union all
select Distinct
    serverproperty('servername')
    , serverproperty('productversion')
    , serverproperty('productlevel')
    ,convert(varchar(30),name) as DBname
    , 'Never Backup, EVER'
    ,NULL
    ,NULL
    ,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2

This is another script, that better displays the missing backups and TLOG intervale of backups. ( source: LINK )

/*
DESCRIPTION    List the age of the last backup for all databases  for FULL, DIFF and Tlog
Plus databases with no backups ever
CONFIGURATION   none
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
 */
SELECT
  DISTINCT
        a.Name AS DatabaseName ,
        CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), datediff(dd,MAX(backup_finish_date),getdate()), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'D'
                            AND is_copy_only = '0'
                 ), 'No Full') AS 'Full (days ago)' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), datediff(dd,MAX(backup_finish_date),getdate()), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'I'
                            AND is_copy_only = '0'
                 ), 'No Diff') AS 'Diff (days ago)' ,
        CASE DATABASEPROPERTYEX(a.name, 'Recovery')
        WHEN 'SIMPLE' THEN '--'
        ELSE
        COALESCE(( SELECT   CONVERT(VARCHAR(20), datediff(hh,MAX(backup_finish_date),getdate()), 120)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'L'
                 ), 'No Log') 
        END AS 'LastLog Hours Ago' ,
        CASE DATABASEPROPERTYEX(a.name, 'Recovery')
        WHEN 'SIMPLE' THEN '--'
        ELSE
        COALESCE(( SELECT   CONVERT(VARCHAR(20), datediff(hh,backup_finish_date,getdate()), 120)
                   FROM     ( SELECT    ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
                                        backup_finish_date
                              FROM      msdb.dbo.backupset
                              WHERE     database_name = a.name
                                        AND type = 'L'
                            ) withrownum
                   WHERE    rownum = 2
                 ), 'No Log')
        END AS 'LastLog2 hours ago'
                 
                 
                 
FROM    sys.databases a
        LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE   a.name <> 'tempdb'
        AND a.state_desc = 'online'
GROUP BY a.Name ,
        a.compatibility_level
ORDER BY a.name