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 dont see any output

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet
$MyQuery = get-content “C:\Users\Security.sql”;
$SqlConnection.ConnectionString = “Server = DBATest; Database = Testdb; Integrated   Security = True”
$SqlCmd.CommandText = $MyQuery;
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$DataSet.Tables[0] | out-file C:\users\outputuser.sql

Could someone point me in right direction ? thanks in advance

Robin16
  • 117
  • 2
  • 4
  • 9
  • 1
    Just a wild guess: `Get-Content` produces an array of strings. You probably need a single string, so try `$MyQuery = Get-Content "C:\Users\Security.sql" | Out-String`. – Ansgar Wiechers Oct 30 '13 at 16:19
  • You could also coerce it to a string with `[string]$MyQuery = Get-Content "C:\Users\Security.sql` – alroc Oct 30 '13 at 17:22

1 Answers1

4

If you have the SQL Server management tools (2008 or newer) installed, this becomes much easier.

add-pssnapin SqlServerCmdletSnapin100;
$myData = invoke-sqlcmd -InputFile "c:\users\security.sql" -serverinstance dbatest -database testdb;
$mydata | out-file c:\users\outputuser.sql;
Remove-PSSnapin SqlServerCmdletSnapin100;

If you have 2012 (or newer) installed, you can skip the snap-in and import the sqlps module:

push-location;
import-module sqlps;
Pop-Location;
$myData = invoke-sqlcmd -InputFile "c:\users\security.sql" -serverinstance dbatest -database testdb;
$mydata | out-file c:\users\outputuser.sql;
remove-module sqlps;

Whether the output file gives you the formatting you're looking for or not, I can't say, as you have specified what you're looking for, nor shown the query itself. You might want to export it to CSV format instead, or use format-table to structure the plain text a bit more, or convert to HTML.

alroc
  • 27,574
  • 6
  • 51
  • 97
  • Thanks everyone for your response...It worked with invoke-sqlcmd... Alroc i have implemented your suggestion and its working but i'm facing one more problem here....when the query saves the output in .sql or .txt file it stores with column name and if i run the same query it throws an error now. Here basic logic is take the user permission backup and run the user backup output after the database restore is complete. – Robin16 Nov 04 '13 at 19:48
  • Example of my user permission output CMD GRANT CONNECT TO [testuser]; GRANT CONNECT TO [testReadWrite]; GRANT CONNECT TO [domain\test]; If you see CMD is the column name which i dont need in the output file ( because if i execute this i'm getting an error ) Please help me on this. Thanks again – Robin16 Nov 04 '13 at 19:53
  • And one more issue here...my query returns two sets of output 1. drop the current database users 2. user and role permissions. when i execute the query it doesnt store anything in the output file but if i remove drop users part output file is storing the user permissions data. Please let me know i confuse you guys....thanks in advance – Robin16 Nov 04 '13 at 19:55
  • Re: your first comment, you can't say "it throws an error" without specifying what "it" is, nor the error message that is thrown. I'm *guessing* that `out-file` is angry because the file already exists and you haven't told it to append or overwrite, so it can't write to the file. – alroc Nov 04 '13 at 20:39
  • You are right...it doesnt throws an error but it's not working. though its a new file it is still not working. Any suggestions on how to avoid column name in the output ? – Robin16 Nov 04 '13 at 20:46
  • 1
    You really ought to open a new question for your output issues. And "it's not working" still isn't a valid explanation of what is happening. – alroc Nov 04 '13 at 20:47
  • Cool thanks I will open a new question. Thanks for your support alroc. – Robin16 Nov 04 '13 at 20:49