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