Verifying SQL Server Backups with PowerShell and SMO

Introduction

Regularly restoring test copies of your databases is the gold-standard proof that your backups work. Between those tests, however, RESTORE VERIFYONLY offers a fast way to confirm that a backup file is readable, that its page checksums are valid, and that the media set is complete. In this post you will see how to run that command from PowerShell by invoking SQL Server Management Objects (SMO), turning a one-off verification into a repeatable step you can schedule across all your servers.

Prerequisites

PowerShell 5 or later is assumed. The SMO assemblies ship with SQL Server and with the feature-pack installers for older versions. If Add-Type –AssemblyName Microsoft.SqlServer.Smo fails, install the “SQL Server SDK” (a small component of the SQL Server Feature Pack) or the “SqlServer” PowerShell module from the PowerShell gallery and restart the host.

The Complete Script

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
# SQL Server instance and backup path
$serverInstance  = ".\sql2019"
$backupFilePath  = "C:\Temp\testdb.bak"

try {
    # Connect to the instance
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverInstance

    Write-Output "Verifying backup file: $backupFilePath"

    # Run RESTORE VERIFYONLY in the context of master
    $server.Databases['master'].ExecuteWithResults(
        "RESTORE VERIFYONLY FROM DISK = '$backupFilePath';"
    )

    Write-Output "Backup verification completed successfully. The file is intact."
}
catch {
    Write-Output "Error encountered during backup verification: $_"
}

Line-by-Line Explanation

Loading SMO

LoadWithPartialName tells .NET to load the SMO assembly into the current run-space. With newer builds the call is technically deprecated, yet it remains the shortest way to bring SMO types into scope without specifying the exact version number of the DLL. Redirecting the method’s return value (| Out-Null) keeps the console clean.

Setting the Parameters

The two variables—$serverInstance and $backupFilePath—make the script reusable. Supply a named instance by using the canonical ServerName\InstanceName format, or a listener name if you want the script to follow an availability-group primary.

Creating the Server Object

SMO’s Server class opens a TDS session under your Windows credentials. Because SMO is just a wrapper over ordinary T-SQL, every action it performs is auditable through sys.dm_exec_sessions and sys.dm_exec_requests.

Running RESTORE VERIFYONLY

The ExecuteWithResults method sends a string of T-SQL to the server and returns any result sets as a DataSet. Running it against the master database avoids locking—or raising a “database is in use” error—on the database contained in the backup. SQL Server reads the file header, validates page checksums, confirms the full media chain, and exits without writing anything to disk.

Error Handling

A try / catch block is critical when you automate verification. Problems such as an inaccessible path, bad permissions, or media damage raise .NET exceptions that you can capture, log, and alert on. The string $_ inside the catch block is the surface error message; use $_.Exception to probe the full stack if you need detail for support tickets.

Making the Script Production-Ready

In practice you will want to verify every backup produced overnight, then re-verify one or two files per week by restoring them onto a test server. Expanding the script is a matter of basic PowerShell:

• Loop through every .bak in a directory tree. Get-ChildItem -Recurse -Filter *.bak feeds each path into the verification routine.
• Read targets from a CSV. With a column for Instance and one for BackupPath, Import-Csv | ForEach-Object { … } becomes your driver.
• Write structured output. Pipe progress and errors to Export-Csv or the Windows Event Log so that monitoring tools can raise an alert if verification fails.
• Schedule it. A SQL Agent PowerShell job step or a Windows Task Scheduler task with the -File switch keeps the process hands-off.

What VERIFYONLY Can and Cannot Prove

RESTORE VERIFYONLY confirms media integrity but it does not guarantee that the backup will restore cleanly on a different server or that all dependencies exist—think certificates, file paths, or availability-group configurations. Always supplement verification with periodic test restores under conditions that mirror production.

Conclusion

Automating backup verification ensures you discover bad files before the day you need them. Because SMO can issue any T-SQL command, the same pattern you saw here scales to restore tests, differential chains, or log-shipping packages. Drop this script into your toolbox, wire it up to an alert, and enjoy the peace of mind that comes from knowing every backup is readable.