How to see backup history through the back-end?

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.