The following PowerShell function worked correctly on PowerShell 2 but now I'm testing it on PowerShell 5 and can't make it work. It is meant to automate backup restore tests. But since SQL Server backup related tasks use messages instead of returning a recordset I had to add a add_InfoMessage with a handler.

I think I could do it with Invoke-Sqlcmd -Verbose but I prefer this method.

I've also searched how to do it with SMO but found nothing

function Restore-Test {
    param (
        [string[]] $bkpPath,
        [string[]] $testServer
    $message = "Inicio: " + (get-date);
    $message += "`nArchivo: " + $bkpPath;
    $message += "`nTestServer: " + $testServer;
    $connString = "Server=" + $testServer + ";Database=master;Integrated Security=SSPI;";
    $commandString = "print 'Message from MSSQL!';";
    #$commandString = "DBA_TOOLS.dbo.sp_RestoreTest N'" + $bkpPath + "'";
    $conn = New-Object System.Data.SqlClient.SqlConnection $connString;
    $cmd = $conn.CreateCommand(); 
    $cmd.CommandText = $commandString ; 
    $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $message += "`n" + $event.Message };
    $conn.FireInfoMessageEventOnUserErrors = $true;
    $cmd.CommandTimeout = 0;
    $cmdResult = $cmd.ExecuteNonQuery();
    $message += "`nExecution result: " + $cmdResult + "`nFinalizacion: " + (get-date);

    return $message;
export-modulemember -function Restore-Test

You will not be able to capture the print statements in the manner you are trying. The handler is meant to output the statement to the console, not to actually capture it.

This would cause the message to write the console, but you will never (at least my testing) be able to append this into your $message variable. Which I don't recall this working like you have it in PowerShell 2.0, but I don't have that version available anymore to try.

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };

Now why this has changed I have not found yet. If I ever do I will try to remember to come update this post.


I finally realized the problem was variable scope. The way around it is to define it as global $global:message = "" and use it inside the event handler with the same reference:

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $global:message += "`n" + $event.Message };

By the way I tried with Invoke-Sqlcmd -Verbose but it has trouble with SQL errors.

You can do it this way:

$message = ""
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $testServer
$ps = [PowerShell]::Create() 
$com = $ps.AddCommand( "Invoke-Sqlcmd" )
$com.AddParameter( "ServerInstance", $serverInstance ) | Out-Null
$com.AddParameter( "Query", $commandString ) | Out-Null
$com.AddParameter( "QueryTimeout", 0 ) | Out-Null
$com.AddParameter( "OutputSqlErrors", $True ) | Out-Null
$com.AddParameter( "IncludeSqlUserErrors") | Out-Null
$com.AddParameter( "SeverityLevel", 0) | Out-Null
$com.AddParameter( "Verbose" )  | Out-Null
$sqlret += $ps.Invoke()  #| Out-Null
$ps.Streams.Verbose | % { $message += "`n" + $_.Message }
$ps.Streams.Error   | % { $message += "`n" + $_.Exception.Message }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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