How to log multiple invoke-sqlcmd verbose outputs into single log file along with showing/not showing them through flag into prowershell prompt


Lets say we have multiple invoke-sqlcmd statements like below:

invoke-sqlcmd -Query "PRINT 'Hello World 1'" -ServerInstance Server\SQL2017 -Database abc -Verbose
invoke-sqlcmd -Query "PRINT 'Hello World 2'" -ServerInstance Server\SQL2017 -Database abc -Verbose
invoke-sqlcmd -Query "PRINT 'Hello World 3'" -ServerInstance Server\SQL2017 -Database abc -Verbose

I want to do the following things:

  1. I want to log verbose output statements of multiple invoke-sqlcmd commands into a single log file
  2. Along with logging into the log file, can we show the verbose output in powershell window too? Also, can we manage it through any flag to show the output in powershell window or not?

Please suggest if it's possible or not.

Best Answer

For 1, you can use PowerShell redirection operators. As per Redirectable output streams, steam 4 is Verbose Stream which is what you want.

invoke-sqlcmd .... -verbose 4>> "D:\temp\yourlogfile.log"

Above will append all your invoke-sqlcmd into just one log file "D:\temp\yourlogfile.log"

your question part 2 is unclear to me.