Sql-server – Retrieving SQL Server InfoMessages in PowerShell 5

powershellsql server

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;
    $conn.Open();
    $cmd = $conn.CreateCommand(); 
    $cmd.CommandText = $commandString ; 
    $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $message += "`n" + $event.Message };
    $conn.add_InfoMessage($handler); 
    $conn.FireInfoMessageEventOnUserErrors = $true;
    $cmd.CommandTimeout = 0;
    $cmdResult = $cmd.ExecuteNonQuery();
    $conn.Close();
    $message += "`nExecution result: " + $cmdResult + "`nFinalizacion: " + (get-date);

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

Best Answer

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.

Related Question