Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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()
}
share|improve this question
3  
invoke-sqlcmd -verbose ? –  Aaron Bertrand Dec 5 '13 at 21:12

1 Answer 1

up vote 4 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 lister attached to the connection manager listing 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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