-- Some queries showing backup history data from the MSDB database
-- usable for SQL 2000/2005/2008

use msdb
go

-- Note: adjust the date field to your choice

SELECT substring(s.database_name,1,20) as "database", (s.backup_size/1024/1024) as "Size_in_MB", s.type,
       s.backup_start_date, s.backup_finish_date, substring(f.physical_device_name,1,30)
FROM   backupset s, backupmediafamily f
WHERE  s.media_set_id=f.media_set_id
AND    s.backup_start_date > '2010-07-07'
ORDER BY s.backup_start_date


SELECT backup_start_date, backup_finish_date, media_set_id,
       type, substring(database_name,1,20)
FROM   backupset

SELECT backup_start_date, backup_finish_date, media_set_id,
       type, database_name
FROM   backupset
WHERE  backup_start_date>'2010-07-07'

SELECT substring(db.name,1,20) AS DatabaseName, max(backup_finish_date) AS LastBackupDate 
FROM
master.dbo.sysdatabases
db LEFT OUTER JOIN 
msdb.dbo.backupset bs 
ON bs.database_name = db.name 
AND bs.type = 'D' 
GROUP BY db.name 
ORDER BY db.name 

select * from sys.database_recovery_status