What RESTORE VERIFYONLY Checks and What It Misses

A successful backup job tells you that a file was written, not that the recovery plan works. RESTORE VERIFYONLY gives an important extra check of a backup set, but its success message has a narrower meaning than a completed restore into a working database.

Understand What RESTORE VERIFYONLY Does

RESTORE VERIFYONLY reads the backup media to check that the backup set is complete and readable without restoring database files. SQL Server also checks available backup checksum information and certain header and destination details. It can find media problems that a successful job log did not reveal. That makes it a useful scheduled check soon after a backup is copied to its retained location.

The command does not rebuild a database and does not test whether applications can connect to the recovered data. I describe its result as evidence that this backup set passed a media level check at this time. A later disk failure, a missing dependency, or a separate logical data problem can still block recovery. The success message is one gate, not a recovery certificate.

Run It Against the Actual Backup File

Verify the retained copy, not merely a temporary local file that will be deleted after transfer. In the example, the path is an illustrative Windows backup location. Adjust it to a file that the SQL Server service can read. A mismatch between the tested file and the file held for recovery makes the test less useful.

Use CHECKSUM to request checksum verification. This is strongest when the backup was created with backup checksums. If no backup checksum exists, WITH CHECKSUM fails the verification instead of manufacturing one after the fact. Review the output and backup metadata rather than treating a quiet command window as proof.

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

Pair Backup and Verify Settings

Create backups with CHECKSUM when the backup design calls for that protection, and retain the job result. During backup, SQL Server validates page checksums where present and calculates a checksum for the backup stream. During verification or restore, the matching backup checksum can help detect damage in the stored backup. Checksums improve detection, but they do not transform every database defect into a detected media error.

This example shows the intended pairing. The example generates a fresh file name, writes a full backup with a backup checksum, and verifies that same file afterward. In production, include the team’s required copy, encryption, naming, and retention settings, and confirm the destination has room before running.

DECLARE @BackupFile nvarchar(260) =
    N'D:\SQLBackups\Sales_full_' +
    CONVERT(nvarchar(36), NEWID()) + N'.bak';
BACKUP DATABASE Sales
TO DISK = @BackupFile
WITH CHECKSUM;
RESTORE VERIFYONLY
FROM DISK = @BackupFile
WITH CHECKSUM;

Recognize the Logical Corruption Limits of RESTORE VERIFYONLY

RESTORE VERIFYONLY does not inspect the restored database’s logical structure as DBCC CHECKDB does. A readable backup can contain a database with damaged relationships or inconsistent internal structures. If the source database already had a problem, a clean media check is not evidence that the data was healthy. Run an appropriate DBCC CHECKDB schedule and investigate failures before relying on that backup generation.

I prefer to test a restored copy because the restore proves that SQL Server can reconstruct the database files from the retained set. After restore, integrity checks and a few application level queries test different layers of confidence. A backup is useful only when the needed data can actually be recovered, not when its file passes a single read.

Check the Entire Restore Chain

A full backup can pass VERIFYONLY while a differential or transaction log backup required for a target time is missing. Each retained file can even pass a separate read check while the sequence does not provide the point in time the business expects. For a full recovery model restore, identify the correct full base, optional matching differential, and uninterrupted log sequence. Practice that exact path.

The msdb history can help map backup sets and log sequence numbers, but it is not a file availability check. Storage can delete a file while history remains. Conversely, a moved backup can still be usable even when the original path no longer exists. The reliable test is to assemble the actual retained files and restore through the chosen target point in an isolated environment.

Test Keys and Destination Dependencies

Encrypted backups need the certificate or asymmetric key and its private key on the restore destination. VERIFYONLY on the source server does not prove that a separate recovery server has the required key material. It also does not prove compatible capacity, access to the retained storage, correct file paths, or enough time to meet a recovery objective. Capture and protect those dependencies in the recovery runbook.

I include a restore on another approved instance in the test schedule. That exercise exposes missing certificates, inaccessible shares, and overlooked server level objects before a real incident. Keep backup keys protected separately from the backup media and verify that authorized staff can retrieve them. A perfectly readable encrypted file is little help when no recovery environment can open it.

Make RESTORE VERIFYONLY Part of a Layered Routine

Use three layers with distinct purposes. Confirm that the backup job completed and the file reached retained storage. Run RESTORE VERIFYONLY WITH CHECKSUM against the retained copy. Periodically restore representative full, differential, and log sequences, run DBCC CHECKDB on the restored database, and exercise critical application queries. Record the selected restore point, duration, failures, and corrective action.

Ask which layer failed rather than marking the entire backup system green or red from one signal. A checksum failure calls for immediate investigation of the file and storage path. A restore failure can expose chain or key problems. An integrity failure points toward the data itself. Each finding needs a different repair, and the real restore test connects them to the outcome that matters.

What does a successful VERIFYONLY result say about the application? Almost nothing about login mappings, SQL Agent jobs, external services, or the queries users need. Include those checks in a recovery exercise. I write down the exact backup files and target instance used for the test so a later operator can repeat it. That record turns a success message into a meaningful recovery drill.

Related reading on this blog: Check Backup Reliability and What does Verify Backup When Finished do in SQL Server? Interview Question of the Week #283.

RESTORE VERIFYONLY is not a restore test, it is one valuable check on the way to one.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.