By querying msdb.dbo.backupset and msdb.obackupmediafamily one can see backup history like start and end time, type of backup, location of the backup.
select bs.database_name
, bs.type
, bs.backup_start_date
, bs.backup_finish_date
, bmf.physical_device_name
FROM msdb.dbo.backupset bs
, msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and backup_start_date > '2010-01-22'
testDB D 2010-01-22 00:00:02.000 2010-01-22 05:09:32.000 c:\Backup\testdb_full.bak
testDB I 2010-01-23 02:26:21.000 2010-01-23 02:30:28.000 C:\Backup\testdbdiff20100123_022621.bak
Type – D = Database, I – Differential Database, L – Log
Documentation on backupset:
SQLServer 2008 – http://technet.microsoft.com/en-us/library/ms186299.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms186299%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260602%28SQL.80%29.aspx
Documentation on backupmediafamily:
SQLServer 2008 http://technet.microsoft.com/en-us/library/ms190284.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms190284%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260605%28SQL.80%29.aspx