In Powershell Retrieving Printed Message From SSMS

Question

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()
}

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. — Peter Vandivier
– Peter Vandivier, Commented May 22, 2018 at 7:18

Michael Green · Accepted Answer · 2018-05-22 00:31:12Z

22

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.

edited May 22, 2018 at 0:31

Michael Green

25.3k13 gold badges54 silver badges100 bronze badges

answered Dec 5, 2013 at 21:41

billinkc

16.1k4 gold badges54 silver badges89 bronze badges

Be careful with this solution as error messages will be shown on host but not caught as exceptions in try / catch: stackoverflow.com/questions/9723808/…

8kb
– 8kb

2024-12-23 21:27:33 +00:00
Commented Dec 23, 2024 at 21:27

Add a comment |

Stack Exchange Network

In Powershell Retrieving Printed Message From SSMS

1 Answer 1

Your Answer

Linked

Hot Network Questions

In Powershell Retrieving Printed Message From SSMS

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions