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.
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,
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
MedFam.Media_Set_ID = MedSet.Media_Set_ID
bkSet.Media_Set_ID = MedSet.Media_Set_ID
--keep your database name in condition
bkSet.Database_Name = 'Adventureworks'
--put the date between which you want to find details of backup
bkSet.Backup_Finish_Date BETWEEN '2011-07-01' AND '2011-07-10'