Using Power-Shell in automated tasks (batch script alternative)

Power Shell it’s the Microsoft’s Automation framework sitting on top of .Net (System.Management.Automation) that lets us perform advanced automated tasks. It can help not only the administrators but also the programmers to save a lot of time by automating repeatable processes. The script’s syntax is quite intuitive and easy to learn so it allows users already writing e.g. batch scripts to grasp it very fast.

If we have for example an old batch file that executes sql scripts stored in the folder as follows:

 @ECHO OFF
 echo script folder: %1
 echo server: %2
 echo database: %3
 echo database user: %4
 echo ----------------

setlocal enabledelayedexpansion
cd /d %1

for %%d in (*.sql) do (
   set filecontent=
   for /f "delims=" %%a in (%%d) do (set "filecontent=!filecontent!%%a")
   set "temp=!filecontent!"
   set "filecontent=!temp:#PARAM#=%4!"
   echo executing %%d
   echo ----------
   sqlcmd -S%2 -E -Q"!filecontent!" -d%3 
)
pause

then the above script can be easily replaced with Power-Shell. Using PS gives us the same flexibility of scripting languages and at the same time allows us to interact with .Net framework when required.

Before we can start executing PS scripts on our computer, we need to enable execution which is disabled by default. We can do it using following cmdlets:

 //enable executing 
 Set-ExecutionPolicy RemoteSigned

 //get back to default settings
 Set-ExecutionPolicy Default

After we’ve created test.ps1 file, we can start implementation. In order to make testing easier we can define default params inside the script using param(…) constructor. Please note that by using [string] keyword we actually creating strongly typed objects. Defined parameters will be overwritten by the user’s passed-in params.

The first command (dir $ScriptFolder) just lists all files within the directory, then ForEach-Object function iterates through all files getting their content into $query variable. Next the $query text is being replaced with our parameter if we have any. The final task is just to execute SQLCMD command passing in query and database connection settings.

param (
[string]$ScriptFolder = "Database\Procedures",
[string]$Server = ".\SQL2008R2",
[string]$Database = "MyDb",
[string]$DatabaseUser = "user"
)


(dir $ScriptFolder) | 
  ForEach-Object { 
    $query = get-content $_.FullName -Raw
    $query = $query.Replace("#PARAM#","user");
    SQLCMD -S $Server -E -Q $query -d $Database
   }

In order to run our Power-Shell script we need just execute following line:

 //command line
 powershell -noexit C:\test.ps1 -ScriptFolder "C:\Database\Procedures" -Server ".\SQL2008R2" -Database "MyDB" -DatabaseUser "user"

Good luck with your Power-Shell scripting 🙂