CODEWITHSUNDEEP
sql-serverpowershell
Robin16
Robin16

Reputation: 117

execute .sql script using powershell and store the output in .sql file

I’m trying to run the sql script .sql file from powershell and save the result into .sql file. Overview : SQL database restore requires a user and permission backup pre-restore and once the restore is complete we need to execute the output( users permissions backup which we did pre-restore ) on the database.

here’s 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 on this ?

Thanks in advance

Upvotes: 4

Views: 12521

Answers (2)

Chris Popieniek
Chris Popieniek

Reputation: 29

Your problem is that you're only capturing one output stream. Your code would work as expected if your query was running "Select 'Hello World!'".

In order to get all 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 turns on a lot of the messages you'd expect to see. The * indicates you want all output streams (you can look up the definitions if you'd like. The verbose stream itself is 4, so 4>&1 would just redirect that one stream). Then you are just redirecting the output to out-file.

Upvotes: 2

David Brabant
David Brabant

Reputation: 43489

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

should do the trick.

Upvotes: 5

Related Questions