Friday, 8 March 2013

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.

      ,CASE A.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS backup_type
      ,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
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.

No comments:

Post a Comment