How to Verify SQL Server Backups with a Simple Query

Ensuring database backups are completed successfully is a critical task for every SQL Server DBA. In this post, I’ll share a SQL script that quickly checks the latest full backup status of all databases on a SQL Server instance.

SQL Script to Verify Backup Completion

• The following script provides:
• The server name
• The database name
• The last backup date
• The backup status (Completed/Not Taken)
• The backup file name

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  
    )  
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  


The script retrieves the most recent full backup (type = 'D') for each database.
It checks if the last backup was completed within the last 24 hours.
If no backup is found in the last 24 hours, it flags it as Not Taken.
The output includes the backup file name for easy tracking.