What does Verify Backup When Finished do in SQL Server? – Interview Question of the Week #283

Question: What does “Verify backup when finished” do in SSMS? It runs a verification of the completed backup. RESTORE VERIFYONLY checks that the backup set is complete and readable, but it doesn’t restore the database or fully check the structure of its data.

I still consider verification a good start. I don’t want the first attempt to read a backup to happen during a disaster. But a successful verification message should lead to the next recovery checks, not end them.

More Than a Header Check, Less Than a Restore Test

VERIFYONLY is more than a header check. It checks readability and completeness, some page-header fields, checksums when present, and destination-space conditions. It doesn’t perform the same structural validation as DBCC CHECKDB.

The SSMS backup dialog has two separate options. “Perform checksum before writing to media” is not the same as “Verify backup when finished.” Taking a backup with CHECKSUM allows later verification to validate the backup checksum. Asking verification for CHECKSUM doesn’t create a checksum that wasn’t recorded.

-- Use a sample database and a new dedicated filename.
BACKUP DATABASE [AdventureWorks2025]
TO DISK = N'D:\SQLLabs\AdventureWorks2025_verify_demo_new.bak'
WITH COPY_ONLY, CHECKSUM;

RESTORE VERIFYONLY
FROM DISK = N'D:\SQLLabs\AdventureWorks2025_verify_demo_new.bak'
WITH FILE = 1, CHECKSUM;

Use your sample database and a fresh filename. The SQL Server service needs folder access. This demonstration neither initializes existing media nor overwrites another backup set. FILE = 1 identifies the first set in this new backup file; a file containing several sets needs the correct position.

The Recovery Test I Recommend

Regularly restore the required backup sequence to a separate test location, keeping production files untouched. Run DBCC CHECKDB on the restored database, validate important application data, and record how long recovery takes. Make sure the required encryption keys or certificates and any dependencies are available.

A successful restore shows more than VERIFYONLY can show. It still doesn’t prove that every application dependency works or that recovery meets the business deadline. Practice the whole recovery procedure.

A verified backup is not a tested restore, it is a readable file, so restore it somewhere before you need it.

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