Check Database backup History, SQL Server

Always I like to use below SQL to find the backup history either DB backup or LOG backup. this is very handy and useful.

By this SQL you can find the backup DB name,  date-time when the backup was taken, media to use, backup-size, logical path to take backup and many more...

By this SQL you also can find the location where to take backup last time ...

Below Query will show the result for lastest 10 days history. If you need to number of the day of history to check, please change in WHERE condition...

SELECT CONVERT(CHAR(100), Serverproperty('Servername')) AS Server,        bkset.database_name,        bkset.backup_start_date,        bkset.backup_finish_date,        bkset.expiration_date,        CASE bkset.type          WHEN 'D' THEN 'Database'          WHEN 'L' THEN 'Log'        END                    AS backup_type,        bkset.backup_size,        bkf.logical_device_name,        bkf.physical_device_name,        bkset.NAME             AS backupset_name,        bkset.description,        * FROM   msdb.dbo.backupmediafamily bkf        INNER JOIN msdb.dbo.backupset bkset                ON bkf.media_set_id = bkset.media_set_id WHERE  ( CONVERT(DATETIME, bkset.backup_start_date, 102)                   >= Getdate() - 10 ) ORDER  BY bkset.database_name,           bkset.backup_finish_date

This SQL will give output as below .. due to some restriction, I am blackout some data ...

Sample Output of the above SQL