Powershell Invoke-Sqlcmd capture verbose output

Question

I'm trying to capture the verbose output from the Invoke-Sqlcmd in Powershell. Anyone got any ideas to do this:

i.e.

Invoke-Sqlcmd  -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose  > D:\SqlLog.txt

The SqlLog.txt file should contain the text "Hello World!"

Answer 1

According to Capture Warning, Verbose, Debug and Host Output via alternate streams:

...if I wanted to capture verbose output in a script:

stop-process -n vd* -verbose 4>&1 > C:\Logs\StoppedProcesses.log

So, you would do something like

(Invoke-Sqlcmd -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose) 4> c:\temp\myoutput.txt

Where 4 is the "verbose" stream.

Answer 2

Since capturing verbose output is not something one can do easily through the native constructs of the PowerShell host, you can always use the programatic access to the PowerShell object. You can then gain access to the five different streams of information:

> $ps = [PowerShell]::Create()
> [ref]$e = New-Object System.Management.Automation.Runspaces.PSSnapInException
> $ps.Runspace.RunspaceConfiguration.AddPSSnapIn( "SqlServerCmdletSnapin100", $e ) | Out-Null
> $ps.AddCommand( "Invoke-Sqlcmd" ).AddParameter( "Query", "Print 'hello world'" ).AddParameter( "Verbose" )
> $ps.Invoke()
> $ps.Streams

Error    : {}
Progress : {}
Verbose  : {hello world}
Debug    : {}
Warning  : {}

> $ps.Streams.Verbose | % { $_.Message | Out-File -Append D:\SqlLog.txt }
> cat D:\SqlLog.txt

hello world