4

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

Robin16
  • 117
  • 2
  • 4
  • 9

2 Answers2

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

David Brabant
  • 41,623
  • 16
  • 83
  • 111
  • Thanks David....now after using your technique output file itself is not generating...Please let me know your ideas... – Robin16 Dec 17 '13 at 17:23
  • Run each statement separately: Invoke-sqlcmd and its parameters first. See if you get any result. Pipe to Format-Table, see if you get any result. Pipe to Out-File (with the -append flag) and see what happens. – David Brabant Dec 17 '13 at 20:11
  • David, nothing is working for me....i have tried running each statement separately....no luck.... – Robin16 Dec 20 '13 at 16:14
2

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.

Chris Popieniek
  • 29
  • 1
  • sorry i didn't mean to downvote but I can't seem to undo it! – user917170 Feb 24 '16 at 01:03
  • The following worked for me as a variant of the above Invoke-Sqlcmd -InputFile "C:\backups\Scripts\Backup Each Database.sql" -ServerInstance $env:ComputerName -database master -verbose 4>> lastbackup.txt Invoke-Sqlcmd -InputFile "C:\backups\Scripts\Generate Copy Script.sql" -ServerInstance $env:ComputerName -database master -verbose 4>> backupcopy.ps1 – user2728841 Aug 27 '17 at 08:07
  • The 4>> allows you to push the output to a file without having "VERBOSE:" stuck on the start of it and it seems to remove the need to fire the powershell script from a parent powershell session – user2728841 Aug 27 '17 at 08:08