The below sql query to get the backup details of all databases on SQL Instance.
use msdb
go
SET NOCOUNT ON
--select 'SERVER NAME : ' + @@servername
select + @@servername as 'Server Name', SUBSTRING(s.name,1,40) AS 'Database Name',
CAST(b.backup_start_date AS char(25)) AS 'Last Backup Date',
CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
THEN 'Backup Completed '
WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
THEN 'Not taken '
ELSE 'Not taken '
END
AS 'Status',
substring(m.physical_device_name ,1,100) AS 'Backup File Name'
from master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'D' ) -- full database backups only, not log backups
left outer join msdb..backupmediafamily m
on m.media_set_id=b.media_set_id
and m.physical_device_name=(select max(physical_device_name) from msdb..backupmediafamily
where media_set_id=b.media_set_id)
WHERE s.name <> 'tempdb'
ORDER BY s.name
