SQL Execution in Powershell: Write Server Messages to Console

It has been a problem for me to work with SQL backup and restore for SharePoint with Powershell…

Issuing the commands is no problem. A simple SQL Server connection is required.

But for long running tasks I need to see the server messages in Powershell.

Here is a trick to do that. I use it since years. As a C# developer I know the “event handler” that can receive the messages…

$cnn = New-Object System.Data.SqlClient.SqlConnection

$cnn.add_InfoMessage([System.Data.SqlClient.SqlInfoMessageEventHandler] {
  param($sender, $event);

  Write-Host $event.Message;
}); 

$cnn.FireInfoMessageEventOnUserErrors = $true

$cnn.ConnectionString = "Server=sqlserver;Database=master;Integrated Security=True;Connection Timeout=86400"

$cnn.Open()

“add_InfoMessage” does the trick.