Broadly, it is of paramount importance to have a script at hand to find out whether or not specific sorts of backups were taken properly as well as how long they last, the size of the backup file, who took the backup, or if the backup was taken using CHECK_SUM option, etc. Today I am sharing that script which returns important information of backups for all databases in a SQL Server instance. In the following script, it filters out info of Full Backups (type='D'). You can use 'L' for Log Backup and 'I' for Differential Backup. So, try it out and make the most out of the info.
SELECT isnull(d.name,'') DatabaseName, CASE is_read_only WHEN 1 THEN 'YES' ELSE 'NO' END as IsReadOnly, cast(sum(size)/128.0/1024.0 as decimal(8,2) ) as DBSize_InGB , isnull( last_backup.recovery_model,'')RecoveryModel,isnull(last_backup.name,'') as BackupName , isnull(cast(last_backup.backup_start_date as varchar(24)),'(No Taken)') as BackupStartDate, isnull(case when [backup_size]>1024 then cast( cast([backup_size]/1024.0 as decimal(8,2)) as varchar(12)) + 'GB' else cast([backup_size] as varchar(12)) + 'MB' end,0) as BackupSize, isnull(case when compressed_backup_size>1024 then cast( cast(compressed_backup_size/1024.0 as decimal(8,2)) as varchar(12)) + 'GB' else cast(compressed_backup_size as varchar(12)) + 'MB' end,0) as CompressedBackupSize , isnull([duration],'') as Duration, isnull(is_copy_only,'') as IsCopyOnly, isnull(is_damaged,'') as IsDamaged, isnull(has_backup_checksums,'') as HasBackupCheckSUM, isnull([user_name],'')as UserName FROM master.sys.sysaltfiles f inner join sys.databases d on f.dbid=d.database_id inner join (select database_name,backup_start_date, name, is_damaged,is_password_protected, has_incomplete_metadata,is_copy_only, cast(backup_size/1024.0/1024.0 as decimal(8,2)) as [backup_size], cast(compressed_backup_size/1024.0/1024.0 as decimal(8,2)) as compressed_backup_size, isnull(cast( CAST((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))/3600 AS varchar) + ' hours ' + CAST(((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))%3600 )/60 AS varchar)+ ' min' as varchar(40) ), '(No Available)') as [duration],bs.backup_finish_date, has_backup_checksums , [user_name], recovery_model FROM msdb.dbo.backupset bs WHERE bs.backup_set_id IN (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset where type = 'D' GROUP BY database_name) AND bs.database_name IN (SELECT name FROM master.dbo.sysdatabases ) ) as last_backup on d.name = last_backup.database_name where fileid<>2 group by dbid,d.name , d.is_read_only,d.recovery_model_desc, last_backup.database_name, last_backup.backup_start_date,last_backup.[backup_size] ,last_backup.compressed_backup_size,last_backup.name, last_backup.duration, last_backup.backup_finish_date,is_copy_only,is_damaged,has_backup_checksums , [user_name], last_backup.recovery_model ORDER BY DATEDIFF(minute, last_backup.backup_start_date, last_backup.backup_finish_date) desc