Powershell Invoke-Sqlcmd capture verbose output

Solution 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.

Solution 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

Solution 3:

Please try:

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

I found it at