12

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()
}
2
  • 4
    invoke-sqlcmd -verbose ? Dec 5, 2013 at 21:12
  • Invoke-SqlCmd ... -Verbose is also nice if you want to redirect the output for later use instead of just printing it to the host immediately. May 22, 2018 at 7:18

1 Answer 1

20

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

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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