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

#Setup
cls
$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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s