Sorry, I'm new to Powershell so I'm obviously missing something fairly fundamental but I can't seem to figure this out.
I've got a powershell script calling a SQL script. I can get the script to run but I would also like to store the output in a log file.
This is my powershell script
Invoke-Sqlcmd -inputfile "testscript.sql" -serverinstance '.\sql2008' | Out-file -filepath "testlog.txt"
And this is my testscript.sql
PRINT 'HELLO WORLD'
At the moment, it creates the testlog.txt file but it is blank.
What am I doing wrong?
It seems the data you want is in the verbose data stream. In PowerShell 3.0 and above, you can redirect the verbose stream (4) to the stdout stream (1) before it can be sent to Out-File.
Invoke-Sqlcmd -inputfile "testscript.sql" -serverinstance '.\sql2008' -verbose 4>&1 | Out-file -filepath "testlog.txt"
Get-Help about_redirection for the explanation of different streams.
See also Powershell: Out-File for discussion about redirecting error and verbose streams to Out-File.
Unfortunately, PowerShell 2.0 only supports redirection of stream 0,1,2. You can use a
[System.Management.Automation.PowerShell] object to get at the other streams. See the accepted answer to Powershell Invoke-Sqlcmd capture verbose output
You can save the Verbose (and all the other console output) to a file without polluting your output stream with Start-Transcript