Find your backup history in SQL Server

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”