Run SQLCMD Scripts Using PowerShell

Many of our production DBs are restored nightly onto a development server which is a great way to test your backups and to have production data available to developers. In order to do this “Prod on Dev” though you have to recreate any DB objects you made yesterday because they got stomped in the refresh. You could make one massive script for all the objects and run it first thing every morning but if you are creating many objects maintenance can become complex. A semi-automated solution is to put each object in its own file and use SQLCMD to run each file either manually or using the Windows Task Scheduler. Better but any time you add an object you have to edit the control script. How about putting each object in a file in the same folder and using PowerShell to get the file names and run each one using SQLCMD? The PowerShell script below does just that.  Schedule it to run from Windows Task Scheduler and you are ready to refresh as needed.

# Get a list of sql files in a folder and run
# them in alphabetical order using SQLCMD.

$Folder = “C:\Temp\Source\”
$SQLInstance = “MyServer\MyDB”

#Get file names to run
$FileNames = Get-ChildItem -Path $Folder -Filter *.sql -Name | Sort-Object

#Run SQLCMD for each file
ForEach ($FileName in $FileNames)

 $File = $Folder + $FileName
sqlcmd -S $SQLInstance -i $File


As you can see, the code is quite simple but let’s take a closer look and consider some potential enhancements.

In #Setup we are clearing the screen and setting variables for future use. These values could easily be input parameters or the whole thing could be turned into a function.

In the #Get file names to run section you retrieve a list of files in the source folder and sort them alphabetically. If they need to be run in a certain order (such as tables before procedures) you can add an index to the file name (010_MyTable.sql). If the files are under source control, say TFS, you could use PowerShell to run TF.exe to get the latest version of the files. Running from the command line in PowerShell is very straightforward as you will see in the next section.

Finally #Run SQLCMD for each file is the code that loops through each file and runs it using the SQLCMD utility.  You could easily add logging and notifications if you have the need.

As you can see, you can automate tedious tasks quickly and easily using PowerShell and SQLCMD.

SQL Server & PowerShell – Finding and Changing Job Owners

We’ve been hearing for a long time that PowerShell (PS) is the wave of the future for SQL Server administration. Under the covers SSMS itself is powered by PS so there ought to be benefits but how do you get started. Everybody has seen how you can right-click a node in the SSMS Object Explorer and jump right in to PS.

But then what?

Let’s consider a small administrative task. Fred creates a bunch of jobs without setting the job owner so he owns them all. He decides to seek greener pastures and when his account is dropped the jobs quit working.

I know you all have (or should have) a script for finding and changing the owner of certain SQL Agent jobs on a SQL instance. They all use a cursor (or something like it) to query msdb.dbo.sysjobs and step through the results, executing msdb.dbo.sp_Update_Job where appropriate. This certainly works but it’s kind of a kludge. Wouldn’t it be more intuitive to say something like “Look at all the jobs, where ‘Fred’ is the owner then change the owner to ‘sa’.”?

That’s really all you do in PS and you can find examples from Mr. Google. However, PowerShell’s ability to use shortcuts and abbreviations often makes figuring out what’s really happening like making sense of alphabet soup.

Here are two snippets of code. The first lists all the jobs on an instance with their owners and the second finds all the jobs with a certain owner and changes that owner to another. Let’s unpack each and see what’s going on.

dir | FT Name, OwnerLoginName –auto

dir” is an alias for “Get-ChildItem” (Also abbreviated “GCI”). It functions like the “dir” command at the DOS prompt – it looks at the current location and returns everything “below”. The important difference is that it doesn’t return a string about the objects, it returns the objects themselves. Keep that in mind as we go on.

|” The pipe character represents the PS pipeline which hands the object(s) to the next command. In our case we are passing all the objects returned by Get-ChildItem to the next command. See this link for more information on the pipeline.

FT” is short for “Format-Table” and is used to format output. The comma separated arguments “Name” and “OwnerLoginName” tell FT which columns to return and the “-auto” automatically prettifies the output for human consumption.

Follow the above screenshots and paste this line of code into the PowerShell window and hit “Enter” to see it for yourself.

Now that we can see who owns each job lets change some owners.

dir | ?{$_.OwnerLoginName –eq “Fred”} | %{$_.OwnerLoginName = “sa”; $_.Alter(); $_.Name}

dir|” functions as above but remember the actual objects are being passed, not just a property value (name, owner, etc.). In this case we don’t need all the jobs, just the ones Fred owns.

?” is an alias for the “Where-Objects” commandlet which filters for the objects you want. The curly brackets (“{}“) contain the script block where you put the filter logic. “$_.” refers to each object coming through the pipe and “OwnerLoginName” is the attribute examined as it passes through the filter. “-eq” is the equality operator (=) while ‘“Fred”‘ is the string it is trying to match.

After this the objects that met the filter criteria are piped to the next command. Again we have an alias, “%“. This is short for “ForEach-Object” and allows you to iterate the objects and change properties or execute methods. In this case we will set (“=“) the “OwnerLoginName” value to the string “sa”. The “Alter()” is a method call that updates any changes. Finally the “Name” prints out the name of each job changed.

As you can see, PowerShell uses an intuitive and object oriented approach to working with SQL Server objects though sometimes shortcuts and aliases can make it a little hard for beginners to interpret. It’s well worth the time to take a careful look at any scripts you download so you know exactly what’s going on. PowerShell has a very nice help system that allows you to do this research. Look here if you’d like some help on Help.

Comparing Multiple Files Using PowerShell

I am moving a number of SSIS configuration files from one folder structure to another as part of a SQL Server upgrade. Both folder structures have each of the files but they point to different file drop locations, SQL Instances, etc. My task was to compare the configuration settings in each environment and map all the changes from the old environment to the new.

Rather than opening each file and visually checking for changes I went with a PowerShell approach centered on the Compare-Object cmdlet. This cmdlet compares two objects and produces a comparison report. This will work whether the two objects are folder structures, text files, or other objects.

My approach was to start with two folders containing .dtsconfig files. First compare the two sets of files and return a list of files that are in both folders. Then, file pair by file pair, open each file and compare contents. Write any differences to an output file. See the comments below for more details.

# Setup
$Path1 = “C:\Path1”
$Path2 = “C:\Path2”
$OutFile = “C:\ComparisonOutputFile.txt”

 # Delete outfile if it exists
If (Test-Path($OutFile)) {Remove-Item $OutFile}

# Write the two paths to the outfile so you know what you’re looking at
“Differences in files of the same name between the following folders:” | Out-File $OutFile -append $Path1 + ” AND ” + $Path2 | Out-File $OutFile
“——————————————–“ | Out-File $OutFile

 # Compare two folders and return only files that are in each
$Dir1 = Get-ChildItem -Path $Path1 -filter *.dtsconfig
$Dir2 = Get-ChildItem -Path $Path2 -filter*.dtsconfig
$FileList = Compare-Object $Dir1 $Dir2 -IncludeEqual -ExcludeDifferent

# Loop the file list and compare file contents
ForEach ($File in $FileList)
{$F1 = $Path1 + “\” + $File.InputObject
$F2 = $Path2 + “\” + $File.InputObject
$ | Out-File $OutFile -append
Compare-Object $(Get-Content $F1) $(Get-Content $F2) | Format-Table
-auto | Out-File $OutFile -append

 As you can see, PowerShell provides significant functionality with very little effort. I was particularly impressed with the comparison report. The SideIndicator column (see below) is a simple way to communicate the differences.   This is a typical output file (the names were changed to protect the innocent).

While it isn’t a fancy GUI it is functional, visual, extensible, and built in. The extensible is important because you don’t get that from a GUI. Suppose I wanted to keep a “Gold Copy” of my config files and run a daily comparison against production to pick up changes? The code above is almost custom written for that process – all it needs is an email step. Further, you could use a similar process using SMO to generate database schema and do you own comparisons. The possibilities are truly endless.