Find your backup history in SQL Server

Saturday, July 16, 2011 |


While managing multiple databases, it is often needed to check database backup history like when did we taken backup? Where did we take last few backups? What was the size of last few backup? And many other information.

I have setup maintenance plan for few database, few databases backup taken by third party software and all works on schedule on particular time even I keep one small T-SQL script handy to check all information I have mentioned above.

SELECT     
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_Name AS BackupPath,
    MedSet.Software_Name AS SoftwareUsedForBackup,
    bkSet.User_Name AS BackupTakenBy,
    bkSet.Server_Name AS ServerName,
    bkSet.Database_Name As DatabaseName,
    CASE bkSet.Type     
            WHEN 'L' THEN 'TransactionLogBackup'
            WHEN 'D' THEN 'FullBackup'
            WHEN 'F' THEN 'FileBackup'
            WHEN 'I' THEN 'DifferentialBackup'
        WHEN 'G' THEN 'DifferentialFileBackup'
        WHEN 'P' THEN 'PartialBackup'
        WHEN 'Q' THEN 'DifferentialPartialBackup'
        ELSE NULL END AS BackupType,
    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB
FROM       
      msdb..BackupMediaFamily MedFam
INNER JOIN 
      msdb..BackupMediaSet MedSet
ON
      MedFam.Media_Set_ID = MedSet.Media_Set_ID
INNER JOIN 
      msdb..BackupSet bkSet
ON
      bkSet.Media_Set_ID = MedSet.Media_Set_ID
WHERE     
      --keep your database name in condition
      bkSet.Database_Name = 'Adventureworks'
AND       
      --put the date between which you want to find details of backup
      bkSet.Backup_Finish_Date BETWEEN '2011-07-01' AND '2011-07-10'
ORDER BY   
      bkSet.Backup_Finish_Date DESC


Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
 http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile

0 comments: