How to verify a SQL Server backup is restorable

A backup job that says "succeeded" tells you a file was written. It does not tell you the database will come back from that file. Verification happens in layers, from cheap checks you run every time to a full test restore you run on a schedule.

Layer 1: CHECKSUM when you back up

WITH CHECKSUM makes SQL Server validate each page's checksum as it reads it, and adds a checksum for the whole backup. If it finds a damaged page, the backup fails loudly instead of quietly saving corruption.

BACKUP DATABASE [Sales]
TO DISK = N'D:\SQLBackups\Sales_FULL_20260927.bak'
WITH CHECKSUM, STATS = 10;

Page checksums only exist if the database's PAGE_VERIFY option is CHECKSUM, the default for databases created on modern versions. Older upgraded databases may still use TORN_PAGE_DETECTION:

SELECT name, page_verify_option_desc FROM sys.databases;
-- ALTER DATABASE [Sales] SET PAGE_VERIFY CHECKSUM;

Layer 2: RESTORE VERIFYONLY

Reads the whole backup file and checks it is complete and readable, re-validating checksums when they are present. It does not create a database.

RESTORE VERIFYONLY
FROM DISK = N'D:\SQLBackups\Sales_FULL_20260927.bak'
WITH CHECKSUM;

Run it against the copy you would actually restore from. That is often the off-site copy after an FTP transfer or download, not the local original.

Layer 3: a real test restore

The only proof that a backup works is restoring it. Restore under a different name, check it, then drop it:

RESTORE DATABASE [Sales_verify]
FROM DISK = N'D:\SQLBackups\Sales_FULL_20260927.bak'
WITH MOVE N'Sales'     TO N'E:\SQLVerify\Sales_verify.mdf',
     MOVE N'Sales_log' TO N'E:\SQLVerify\Sales_verify_log.ldf',
     CHECKSUM, STATS = 10;

DBCC CHECKDB ([Sales_verify]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Spot-check the data your business depends on
SELECT COUNT(*) AS invoices, MAX(InvoiceDate) AS newest FROM [Sales_verify].dbo.Invoices;

DROP DATABASE [Sales_verify];

DBCC CHECKDB on the restored copy checks the database's internal consistency without loading your production server. The spot-check query confirms the backup is as recent as you expect. Adjust the table and column names to your own schema.

How often

Catch the silent failure: backup age

Jobs sometimes stop running without an error: a disabled task, an expired password, a server that was off. Check the age of the newest full backup per database and alert when it is too old:

SELECT d.name,
       MAX(b.backup_finish_date) AS last_full,
       DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) AS hours_old
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name AND b.type = 'D'
WHERE d.name <> 'tempdb'
GROUP BY d.name
HAVING MAX(b.backup_finish_date) IS NULL
    OR DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) > 26;

Any row returned is a database without a full backup in the last 26 hours.

Write the restore down

Keep a one-page restore procedure next to the backups: where the files are, which credentials open the off-site copy, the restore commands, and who to call. Test it with someone other than the person who set it up. Restores usually happen under pressure, and that is the wrong time to work it out.