Our DBA team doesn't verify the backups using the below TSQL (which can easily be done post-backup and takes almost no time, so I don't get why not):

RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\LOCATION'

They've had issues in the past, so even though we'd think they'd learn from it, they haven't. I created a Powershell script to do this because we have about 100+ servers and I just want to run this script against all the backups just to ensure they're valid. The below script runs correctly (in that it doesn't break or throw errors), I am curious if there's a way to get the printed message back in Powershell that we would normally get in SSMS where it states The backup set on file 1 is valid as verification.

$SqlCon = New-Object System.Data.SqlClient.SqlConnection
$SqlCon.ConnectionString = "SERVER=SERV\INST;Integrated Security=true;DATABASE=master"

$baks = Get-ChildItem "D:\Backups\" -Filter *.BAK

foreach ($bak in $baks)
{
    $SqlCon.Open()
    $cd = New-Object System.Data.SqlClient.SqlCommand
    $cd.Connection = $SqlCon

    $cd.CommandText = "RESTORE VERIFYONLY FROM DISK = @f"
    $cd.Parameters.Add("@f", $bak.FullName)
    $cd.ExecuteNonQuery()
    $SqlCon.Close()
}
up vote 15 down vote accepted

Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell

The trick, as Jonathan points out, is that you need to have a listener attached to the connection manager listening for the print or error actions.

$cd.Parameters.Add("@f", $bak.FullName) | Out-Null
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message }
$SqlCon.add_InfoMessage($handler)
$SqlCon.FireInfoMessageEventOnUserErrors = $true  

I also piped the output of adding a parameter to Out-Null as that was noise I didn't need.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.