SQL Server Verify Backup Files: RESTORE VERIFYONLY

calendar Apr 6, 2026 / · 8 min read · sql server backup recovery database administration maintenance scripts restore verifyonly msdb sql scripts database management backup validation msdb.dbo.backupset msdb.dbo.backupmediafamily  ·
Share on: linkedin copy

Verify SQL Server Backup Files with RESTORE VERIFYONLY

Backup files that cannot be restored are worthless, and many DBAs discover problems only when they attempt an actual restore under pressure. The scripts in this article use RESTORE VERIFYONLY to confirm that backup files are readable and internally consistent before a real disaster occurs.

Purpose and Overview

RESTORE VERIFYONLY reads a backup file and validates its structure without writing any data to a database. It checks that the backup set is complete, that all volumes are readable, and that header metadata is intact. It does not verify that every data page is logically consistent, but it catches the most common failure modes: truncated files, corrupted headers, and missing media families.

The two scripts below cover the two most common verification workflows: checking a single known file, and automatically looping through all full backups recorded in msdb for the past seven days.

Code Breakdown

Below is the full script set, followed by a section-by-section explanation.

 1-- =============================================
 2-- Script 1: Verify a single backup file
 3-- =============================================
 4RESTORE VERIFYONLY
 5FROM DISK = N'C:\Backups\YourDatabaseName_Full.bak'
 6WITH NOUNLOAD;
 7
 8-- =============================================
 9-- Script 2: Verify all recent backups from msdb
10-- Loops through backup files recorded in msdb
11-- and verifies each one
12-- =============================================
13SET NOCOUNT ON;
14
15DECLARE @BackupFile   NVARCHAR(500),
16        @VerifySQL    NVARCHAR(600),
17        @DatabaseName SYSNAME,
18        @BackupDate   DATETIME;
19
20-- Results table
21CREATE TABLE #VerifyResults (
22    DatabaseName  SYSNAME,
23    BackupDate    DATETIME,
24    BackupFile    NVARCHAR(500),
25    VerifyStatus  VARCHAR(10),
26    Message       NVARCHAR(500)
27);
28
29DECLARE VerifyCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
30    SELECT DISTINCT
31        bs.database_name,
32        bs.backup_finish_date,
33        bmf.physical_device_name
34    FROM msdb.dbo.backupset bs
35    JOIN msdb.dbo.backupmediafamily bmf
36        ON bs.media_set_id = bmf.media_set_id
37    WHERE bs.type = 'D'                              -- Full backups only
38      AND bs.backup_finish_date >= DATEADD(DAY, -7, GETDATE()) -- Last 7 days
39      AND bmf.device_type = 2                        -- Disk files only
40    ORDER BY bs.database_name, bs.backup_finish_date DESC;
41
42OPEN VerifyCursor;
43FETCH NEXT FROM VerifyCursor INTO @DatabaseName, @BackupDate, @BackupFile;
44
45WHILE @@FETCH_STATUS = 0
46BEGIN
47    BEGIN TRY
48        SET @VerifySQL = N'RESTORE VERIFYONLY FROM DISK = N''' + @BackupFile + N''' WITH NOUNLOAD;';
49        EXEC sp_executesql @VerifySQL;
50
51        INSERT INTO #VerifyResults VALUES (@DatabaseName, @BackupDate, @BackupFile, 'PASSED', 'Backup is valid');
52    END TRY
53    BEGIN CATCH
54        INSERT INTO #VerifyResults VALUES (@DatabaseName, @BackupDate, @BackupFile, 'FAILED', ERROR_MESSAGE());
55    END CATCH
56
57    FETCH NEXT FROM VerifyCursor INTO @DatabaseName, @BackupDate, @BackupFile;
58END;
59
60CLOSE VerifyCursor;
61DEALLOCATE VerifyCursor;
62
63SELECT * FROM #VerifyResults ORDER BY VerifyStatus, DatabaseName;
64DROP TABLE #VerifyResults;

Script 1: Single File Verification

1RESTORE VERIFYONLY
2FROM DISK = N'C:\Backups\YourDatabaseName_Full.bak'
3WITH NOUNLOAD;

This is the simplest form of backup verification. Replace the file path with the actual path to your .bak file. WITH NOUNLOAD tells SQL Server not to rewind or unload a tape device after reading — it has no effect on disk files but is included here as a safe habit inherited from tape-era scripts and does no harm. If the file passes, SQL Server prints The backup set on file 1 is valid. If it fails, it raises an error with a description of the problem.

Script 2: Variables and Results Table

 1DECLARE @BackupFile   NVARCHAR(500),
 2        @VerifySQL    NVARCHAR(600),
 3        @DatabaseName SYSNAME,
 4        @BackupDate   DATETIME;
 5
 6CREATE TABLE #VerifyResults (
 7    DatabaseName  SYSNAME,
 8    BackupDate    DATETIME,
 9    BackupFile    NVARCHAR(500),
10    VerifyStatus  VARCHAR(10),
11    Message       NVARCHAR(500)
12);

Four variables hold the values fetched from the cursor on each iteration. The temporary table #VerifyResults accumulates one row per backup file, recording whether it passed or failed and the error message if it failed. Using a temp table instead of printing results inline means you get a clean, sortable result set at the end.

Script 2: Cursor Query

 1DECLARE VerifyCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
 2    SELECT DISTINCT
 3        bs.database_name,
 4        bs.backup_finish_date,
 5        bmf.physical_device_name
 6    FROM msdb.dbo.backupset bs
 7    JOIN msdb.dbo.backupmediafamily bmf
 8        ON bs.media_set_id = bmf.media_set_id
 9    WHERE bs.type = 'D'
10      AND bs.backup_finish_date >= DATEADD(DAY, -7, GETDATE())
11      AND bmf.device_type = 2
12    ORDER BY bs.database_name, bs.backup_finish_date DESC;

The cursor joins msdb.dbo.backupset to msdb.dbo.backupmediafamily on media_set_id. This join is necessary because backupset records metadata about the backup operation while backupmediafamily records the physical file path in physical_device_name.

Three WHERE filters limit the scope:

  • bs.type = 'D' restricts results to full database backups. Use 'L' for log backups or 'I' for differential backups.
  • DATEADD(DAY, -7, GETDATE()) limits the check to the last seven days. Adjust the interval to match your retention policy.
  • bmf.device_type = 2 keeps only disk-based backups and skips tape devices or virtual device interfaces used by third-party backup tools.

The cursor is declared LOCAL STATIC READ_ONLY FORWARD_ONLY — the most efficient cursor type for a simple sequential read that does not modify data.

Script 2: Verification Loop

 1WHILE @@FETCH_STATUS = 0
 2BEGIN
 3    BEGIN TRY
 4        SET @VerifySQL = N'RESTORE VERIFYONLY FROM DISK = N''' + @BackupFile + N''' WITH NOUNLOAD;';
 5        EXEC sp_executesql @VerifySQL;
 6        INSERT INTO #VerifyResults VALUES (@DatabaseName, @BackupDate, @BackupFile, 'PASSED', 'Backup is valid');
 7    END TRY
 8    BEGIN CATCH
 9        INSERT INTO #VerifyResults VALUES (@DatabaseName, @BackupDate, @BackupFile, 'FAILED', ERROR_MESSAGE());
10    END CATCH
11
12    FETCH NEXT FROM VerifyCursor INTO @DatabaseName, @BackupDate, @BackupFile;
13END;

Each iteration builds a RESTORE VERIFYONLY statement as a string and executes it with sp_executesql. The BEGIN TRY / BEGIN CATCH block is critical: without it, a single failed verification would abort the entire loop. With it, the error message is captured in ERROR_MESSAGE() and written to the results table, and the loop continues to the next file. After the loop closes and deallocates the cursor, the results are selected ordered by VerifyStatus so any FAILED rows appear first.

Key Benefits and Use Cases

  • Proactive failure detection. Running this script nightly or weekly reveals broken backup files before a disaster recovery situation.
  • Audit evidence. The results table provides a timestamped record of which files were checked and whether they passed. This is useful for compliance reporting.
  • Coverage across all databases. Script 2 automatically includes every database that has a backup recorded in msdb within the time window — no manual list to maintain.
  • Non-destructive. RESTORE VERIFYONLY never modifies any database. It is safe to run on production instances at any time.
  • Isolation of failures. The TRY/CATCH pattern means one corrupt file does not prevent the rest from being checked.

Performance Considerations

RESTORE VERIFYONLY reads the entire backup file sequentially. For large backup files (hundreds of gigabytes), this generates substantial disk I/O on the storage holding the backups. Consider the following:

  • Schedule off-peak. Run the verification script during low-activity windows to avoid competing with production I/O or the backup jobs themselves.
  • Network paths. If backup files are on a network share, verification reads the file across the network. This can be slow and may affect network bandwidth. Test the duration before scheduling in production.
  • Parallelism. The cursor loop is single-threaded. If you have many backup files to check, consider breaking the workload into smaller date ranges run in parallel sessions.
  • Time window. The default seven-day window in Script 2 may include a large number of files on busy servers with many databases. Narrow the window or filter to specific databases if runtime is a concern.

Practical Tips

  • Change the date window. Replace -7 in DATEADD(DAY, -7, GETDATE()) with the number of days that matches your backup retention policy. If you keep 30 days of backups, verify the full 30 days weekly.
  • Add log and differential backups. Duplicate the cursor query with bs.type = 'L' and bs.type = 'I' to cover all backup types in your recovery chain.
  • Filter to specific databases. Add AND bs.database_name = N'YourDatabase' to the cursor WHERE clause to scope the check to one database during testing.
  • Send failure alerts. After the final SELECT, add logic to check whether any VerifyStatus = 'FAILED' rows exist and use msdb.dbo.sp_send_dbmail to email the DBA team if problems are found.
  • File path validation. RESTORE VERIFYONLY will fail with a file-not-found error if the backup file has been moved or deleted since the msdb record was created. These FAILED rows in the results table are also useful for identifying stale msdb records.
  • Third-party backup tools. Some backup agents (Veeam, Commvault, NetBackup) use virtual device interfaces. These produce device_type values other than 2 in backupmediafamily and are excluded by the current filter. Adjust the filter or consult your vendor documentation to verify files created by those tools.

Conclusion

RESTORE VERIFYONLY is one of the most straightforward and highest-value tools available to a SQL Server DBA. The single-file script in Script 1 is useful for spot checks after a specific backup completes. Script 2 scales that check across the entire backup history recorded in msdb, providing systematic coverage with minimal setup. Running these scripts regularly as part of your maintenance plan ensures that backup files are trustworthy and that recovery procedures will work when they are needed most.

References

Posts in this series