Script to get the dababase backup history.

Often I have to get the backup history for reporting purpose. So I have writtent small script which give all details of database back history which is required for my report. I have used backupset and backupmediafamily tables to get the database backup history. Both of these tables resides in msdb database. So for some reason if you have restored msdb database. Then it will show history till the restored database time. Please see below script to get the database backup history.
 

SELECT

      A.database_name

      ,CASE A.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS backup_type

      ,B.physical_device_name

      ,CAST(CAST(A.backup_size / (1024*1024) AS INT) AS VARCHAR) + ' ' + 'MB' AS size

      ,CAST(DATEDIFF(S, A.backup_start_date, A.backup_finish_date) AS VARCHAR) + ' ' + 'Seconds' time_taken

      ,A.backup_start_date

FROM msdb.dbo.backupset A

      INNER JOIN msdb.dbo.backupmediafamily B ON A.media_set_id = B.media_set_id

ORDER BY A.database_name

      , backup_start_date DESC

 

This script gives history of all database, if you need details for only one database then restict result by using WHERE caluse on database_name field. 

 

For more information on backupset and backupmediafamily tables, see below links.

http://msdn.microsoft.com/en-us/library/ms186299.aspx

http://msdn.microsoft.com/en-us/library/ms190284.aspx