Run a .sql script using powershell and save the output in a .sql file

Question

I am trying to run the sql script.sql file from powershell and save the result in a .sql file. Overview: restoring a SQL database requires a preliminary restoration of the user's backup and permissions, and after the restoration is complete, we need to output (back up the user permissions that we previously performed) to the database.

heres my script, and when I execute, I see an empty file.

Add-PSSnapin SqlServerProviderSnapin100; $server = 'DBA_Test'; $database = 'Test'; $mydata = invoke-sqlcmd -inputfile "C:\users\security.sql" -serverinstance $server -database $database | Format-Table -HideTableHeaders -AutoSize $mydata | out-file C:\users\output.sql; Remove-PSSnapin SqlServerCmdletSnapin100; 

Can someone help me with this?

Thanks in advance

Answer 1

 invoke-sqlcmd -inputfile "C:\users\security.sql" -serverinstance $server -database $database | Format-Table -HideTableHeaders -AutoSize >> C:\users\output.sql 

or

 Invoke-sqlcmd -inputfile "C:\users\security.sql" -serverinstance $server -database $database | Format-Table -HideTableHeaders -AutoSize | Out-File –FilePath C:\users\output.sql –Append 

gotta do the trick.

Answer 2

Your problem is that you only commit one output stream. Your code will work as expected if your query is run "Select" Hello World! "

To get all the output streams (verbose, error and output) into a single file, you can do the following:

 invoke-sqlcmd -inputfile "C:\users\security.sql" -serverinstance $server -database $database -verbose *>&1 | out-file C:\users\output.sql 

The -verbose flag includes many messages that you expect to see. * Indicates that you want all output streams (you can find definitions if you want. The verbal stream itself is 4, so 4> and 1 will simply redirect this stream). Then you just redirect the output to a file.