Regularly restoring test copies of your databases is the gold-standard proof that your backups work. Between those tests, however, RESTORE VERIFYONLY<\/em> 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.<\/p>\n PowerShell\u00a05 or later is assumed. The SMO assemblies ship with SQL Server and with the feature-pack installers for older versions. If Add-Type \u2013AssemblyName\u00a0Microsoft.SqlServer.Smo<\/strong> fails, install the \u201cSQL Server SDK\u201d (a small component of the SQL Server Feature Pack) or the \u201cSqlServer\u201d PowerShell module from the PowerShell gallery and restart the host.<\/p>\n The two variables\u2014 SMO\u2019s The A 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:<\/p>\n RESTORE VERIFYONLY<\/em> confirms media integrity but it does not guarantee that the backup will restore cleanly on a different server or that all dependencies exist\u2014think certificates, file paths, or availability-group configurations. Always supplement verification with periodic test restores under conditions that mirror production.<\/p>\n Automating backup verification ensures you discover bad files before<\/em> 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[35,54,53],"tags":[565,86,564,57,566,216,131],"class_list":["post-1079","post","type-post","status-publish","format-standard","hentry","category-data-integrity","category-maintenance","category-powershell","tag-backup-verification","tag-powershell","tag-restore-verifyonly","tag-scripting","tag-server-maintenance","tag-smo","tag-sql-server"],"yoast_head":"\nPrerequisites<\/h2>\n
The Complete Script<\/h2>\n
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null\n# SQL Server instance and backup path\n$serverInstance = \".\\sql2019\"\n$backupFilePath = \"C:\\Temp\\testdb.bak\"\n\ntry {\n # Connect to the instance\n $server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverInstance\n\n Write-Output \"Verifying backup file: $backupFilePath\"\n\n # Run RESTORE VERIFYONLY in the context of master\n $server.Databases['master'].ExecuteWithResults(\n \"RESTORE VERIFYONLY FROM DISK = '$backupFilePath';\"\n )\n\n Write-Output \"Backup verification completed successfully. The file is intact.\"\n}\ncatch {\n Write-Output \"Error encountered during backup verification: $_\"\n}<\/code><\/pre>\nLine-by-Line Explanation<\/h2>\n
Loading SMO<\/h3>\n
LoadWithPartialName<\/code> 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\u2019s return value (| Out-Null<\/code>) keeps the console clean.<\/p>\nSetting the Parameters<\/h3>\n
$serverInstance<\/code> and $backupFilePath<\/code>\u2014make the script reusable. Supply a named instance by using the canonical ServerName\\InstanceName<\/code> format, or a listener name if you want the script to follow an availability-group primary.<\/p>\nCreating the
Server<\/code> Object<\/h3>\nServer<\/code> 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<\/em> and sys.dm_exec_requests<\/em>.<\/p>\nRunning RESTORE VERIFYONLY<\/em><\/h3>\n
ExecuteWithResults<\/code> method sends a string of T-SQL to the server and returns any result sets as a DataSet<\/code>. Running it against the master<\/em> database avoids locking\u2014or raising a \u201cdatabase is in use\u201d error\u2014on 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.<\/p>\nError Handling<\/h3>\n
try \/ catch<\/code> 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 $_<\/code> inside the catch block is the surface error message; use $_.Exception<\/code> to probe the full stack if you need detail for support tickets.<\/p>\nMaking the Script Production-Ready<\/h2>\n
\n
Get-ChildItem -Recurse -Filter *.bak<\/code> feeds each path into the verification routine.<\/li>\nInstance<\/code> and one for BackupPath<\/code>, Import-Csv | ForEach-Object { \u2026 }<\/code> becomes your driver.<\/li>\nExport-Csv<\/code> or the Windows Event Log so that monitoring tools can raise an alert if verification fails.<\/li>\n-File<\/code> switch keeps the process hands-off.<\/li>\n<\/ul>\nWhat VERIFYONLY<\/em> Can and Cannot Prove<\/h2>\n
Conclusion<\/h2>\n