SQL Server Database backup verify script

--Verify Backup is completed or not

SET NOCOUNT ON

select 'SERVER NAME : ' + @@servername

select 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