Query to get the latest FULL backup details

 

SELECT   cast ((backup_finish_date - backup_start_date) AS TIME(0)) as DURATION, SERVER_NAME,b.DATABASE_NAME,[TYPE],backup_set_id,     b.backup_start_date,b.backup_finish_date, Round(b.backup_size/(1024*1024),2) as Size_n_MB, Physical_device_name   FROM msdb.dbo.backupset b,   ( select a.DATABASE_NAME, MAX(a.backup_set_id) id   from msdb.dbo.backupset a  WHERE a.TYPE = 'D' and  a.DATABASE_NAME = DATABASE_NAME  group by a.DATABASE_NAME ) cc,   msdb.dbo.backupmediafamily f WHERE TYPE = 'D' AND b.backup_set_id = cc.id   AND b.media_set_id = f.media_set_id