February 3, 2010 at 10:23 am
Hi i am new to Powershell and was hoping someone could help
Scenario:
I have one sql script per database called testDB1DBPerms.sql, testDB2DBPerms.sql, testDB3DBPerms.sql, testDB4DBPerms.sql, masterDBPerms.sql, modelDBperms.sql etc stored in a shared file location \\location\scripts\
I have number of databases on my server (note no testDB4 database)
testDB1
testDB2
testDB3
master
model
etc
I wanted to run the correct script against the correct database. Each script has 'USE DB' in the name so I thought it would be easy as I could pipe each script into a variable using Get-ChildItem and then use Invoke-SQLCmd to run each script against the SQL Server as below (obtained from Martin Bell's SQL Server MVP Blog).
foreach ($f in Get-ChildItem -path "\\serveraa\temp\SQLScripts\" -Filter *.sql | sort-object -desc )
{
$out = "C:\temp\SQLScripts\OUTPUT\" + $f.name.split(".")[0] + ".txt" ;
invoke-sqlcmd -InputFile $f.fullname | format-table | out-file -filePath $out
}
Only problem is if I load "testDB4DBPerms.sql" into a variable and then run against a server where "testDB4" database does not exist then the script will fail when it says USE testDB4 in the script
I wanted to load all the files into a variable and then only if the truncated stored name upto 'DBPerms.sql' match a valid database on the server then execute the script. The script can then run on the server and it doesn't have to run in the context of a database as the USE DBName command will be in the script.
hope this makes sense
February 3, 2010 at 3:21 pm
I am not sure if this is what you are looking for, but you could try this:
- Load all of the sql scripts into an array
- Connect to the SQL server and run get-childitem to get the database names and put them in an array
- Run through each element in the database name array and do a match to the script array (using something like substring to strip out the file extention); if there is a match, add the file to another array (let's call it the run array)
- process each element in the run array, passing the script name to invoke-sqlcmd
Is that kind of what you are looking for?
Joie Andrew
"Since 1982"
February 3, 2010 at 3:36 pm
That makes sense. Was hoping I could get some help with the syntax?powershell is totally new to me...
Thanx
February 4, 2010 at 6:16 pm
Which part are you trying to figure out how to do? If you want to create an array with files it would be something like this:
$fileArray = get-childitem directory-name
If I get some time soon, I will try and script out something and see if it is what you are looking for.
Joie Andrew
"Since 1982"
February 5, 2010 at 2:12 am
I understand the programming logic but need a bit of a pointer in the right direction when its comes to creating the code. I will give it a go and if you can get a chance that will be appreciated.
Thanks 🙂
February 10, 2010 at 6:22 pm
Try this:
#Create empty array
$runArray = @()
net use x: \\location\scripts
$scriptArray = get-childitem x:
net use x: /delete
set-location sqlserver:<dbserver>
$dbArray = get-childitem .
#Go through each script and see if it matches databases in the dbArray
foreach ($script in $scriptArray)
{
foreach ($db in in $dbArray)
{
if ($db -like $script)
{
$runArray += $script
}
}
}
#Go through the runArray running each script
foreach ($element in $runArray)
{
<commands to run script (which is represented by $element)>
}
it is important to note that I have NOT tested any of this, but the logic should be mostly there. You will have to replace items I put in <> symbols.
Joie Andrew
"Since 1982"
February 11, 2010 at 1:58 am
thank you very much! It gives me something to go on 🙂
February 11, 2010 at 12:53 pm
I just thought about this after I posted it. On this section:
{
if ($db -like $script)
{
It will not add anything to the $runArray as it is because the strings will not match. You will either need to compare it to a substring or look at doing the comparison with the -match operator. For more information on that, run the following and research the article given in powershell:
get-help about_comparison_operators | more
Joie Andrew
"Since 1982"
February 17, 2010 at 11:19 am
Here is the script I modified slightly from the above post
Set-Location C:$scriptArray = Get-ChildItem \\filesharelocation
$runArray = @()
Set-Location SQLSERVER:\SQL\SERVERNAME1\DEFAULT\Databases
$dbArray = Get-ChildItem -name
foreach ($script in $scriptArray)
{
foreach ($db in $dbArray)
{
if ($db -match $script)
{
$runArray += $script
}
}
}
The contents of $scriptArray are masterDBPerms.sql, msdbDBPerms.sql, database1DBPerms.sql, database2DBPerms.sql, database3DBPerms.sql etc
the contents of $dbArray are master, msdb, database1, database3 etc (note there is no database2
I thought that -match would macth 'master' to 'masterDBPerms.sql' and likewise for all such that I would end up with another Array ($runArray) with all scripts except database2DBPerms.sql as the database2 does nto exist on that server.
Instead I am getting the following error
The '-match' operator failed: parsing "\\filesharelocation\masterDBperms.sql" - Unrecognized escape sequence \L..
At G:\test.ps1:15 char:17
+ if ($db -match <<<< $script)
i am getting this repeatedly for every loop.
Any ideas?
Thanks
February 18, 2010 at 3:51 pm
Okay, a couple of things:
First, the line using -match is incorrect. You are evaluating the elements in the array, but you are not comparing them to a value, so the way it is now $runArray will never have anything in it. This line:
if ($db -match $script)
Should look like this:
if ($db -match $script -eq "True")
That is probably my fault, I did not put that in my example. Sorry.
Try that and see if it works. If it doesn't, then my second question is is \\filesharelocation the actual name of the location of the sql scripts, or is it another name? The reason I ask is because your error does not seem to match what you posted. Also, is that the exact and entire error message that you receive when you run the script?
Joie Andrew
"Since 1982"
December 13, 2010 at 10:20 am
Many thanks for all your help on this and pointing me in the right direction. I finally got round to getting the time to complete the solution and add other things such as error logging, capturing and listing of all run scripts to a log.
Heres what worked for me in the end (just the code snippet thats relevant to the question). I list two ways it can be done using -match and Startswith()
$SName = gc env:computername
Set-Location C:$DBScriptsArray = Get-ChildItem -Path $DBScriptsPath -Include *.sql -Exclude "tempdb*", "master*", "model*", "msdb*", "DBA*"
$runArray = @()
Set-Location SQLSERVER:\SQL\SERVERNAME1\DEFAULT\Databases
$dbArray = Get-ChildItem -name -Exclude "tempdb*", "master*", "model*", "msdb*", "DBA*"
foreach ($script in $DBScriptsArray)
{
foreach ($db in $dbArray)
{
$DBScriptName = $db + "_DBPerms"
if ($script.name.StartsWith($DBScriptName) -eq "TRUE")
# ($script.name -match $DBScriptName -eq "TRUE") #Another Way
{
Invoke-Sqlcmd -ServerInstance $SName -InputFile $script -SuppressProviderContextWarning -ErrorAction stop
}
}
}
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply