Creating PowerShell Scripts

Much like batch files for the DOS command prompt, PowerShell scripts give administrators a great way to automate routine processes and gather data. PowerShell scripts, unlike batch files, have a great deal of flexibility because PowerShell can access a great deal of system objects, and support various advanced parameterization and logic control features. In this post, we’ll look at creating a PowerShell script from scratch by accessing and manipulating objects on a SQL Server.
First things first; you’ll need to ensure that your environment will run your scripts. You see, with great power comes great responsibility; PowerShell can do a lot of things, but how do you make sure the script is executed in a safe and secure environment so its power can’t be abused? Fortunately, PowerShell has some built in security that you’ll need to set before you can start running scripts.

Execution Policy

Probably the first thing you need to look at/think about is the Execution Policy. If you open a PowerShell prompt and type executionpolicy (which is an alias for the cmdlet Get-ExecutionPolicy), you’ll get one of the following responses:

• Restricted: This is the default policy. No scripts can be run.
• AllSigned: This policy will only allow scripts that have been signed by a trusted publisher to run, even if the script was written on the computer on which it is being run.
• RemoteSigned: This policy will allow locally written scripts run, but anything from the Internet must be signed by a trusted publisher.
• Unrestricted: Runs everything. Will still prompt for permission to run a downloaded script.
• Bypass: Runs everything; no prompts.
• Undefined: This removes the currently effective policy.

Before you can run any scripts, you’ll need to change this. For initial development purposes, I’d suggest using RemoteSigned, because it will at least protect you from running something on your machine that was downloaded from the Internet. If you’re confident in what you are doing, go ahead and use Unrestricted, but ONLY for development. And even then, its always best to develop your scripts with the same security settings you’ll eventually use in production, that way you can be sure to develop a secure script!
One other note: There are separate execution policy settings for scripts run from SQLPS and PowerShell; be sure to set the correct environment. For our purposes, we’ll be working from SQLPS, so be sure to work from the SQLPS prompt from this point on.
Setting the policy is easy:
Set-ExecutionPolicy RemoteSigned



Easy enough. So now that the environment is setup, its time to create a script!

Creating a Script

A PowerShell script is simply a text document that contains a series of cmdlet executions (with some control logic around it). So to create a PowerShell script, simply create a new text file, and name it something obvious, such as “MyFirstPowerShellScript.ps1”. Note the extension;PowerShell scripts are recognized by the extension *.ps1, so be sure to change this from the default *.txt extension.
In order to get your feet wet, let’s create a script that does some very basic tasks:

• Return a list of SQL related services running on a given machine.
• Return a list of database (omitting the system databases).
• Sort the data into a readable format.

First, let’s retrieve the list of services from a remote server (if you read the post previous to this, it should look familiar):
get-service –computername CSSRVSQLTST |
where-object {$_.DisplayName –match “^SQL.*”}


From the command line, you should see the same thing as the above screenshot. But what about a script?

PowerShell_ise.exe

As of PowerShell 2.0, PowerShell ships with a nice new integrated scripting environment (ISE). You can get to this environment (if you have PS2.0) by typing powershell_ise.exe at the command prompt, run box, or Start menu. So, you can use the ISE if you’d like, but if not, you can literally create a new file on your disk somewhere, and give it a *.ps1 extension, and then edit the file in any text editor you’d like. I’ll be using powershell_ise.exe, so that’s the screenshots you’ll see.

Simple Script

Let’s look at getting our service list and outputting it to a file. Along the way, we’ll talk briefly about variables and outputting results. Here’s a quick script:

First off, notice the commented lines, prefixed with the pound (or hash) sign (#). This gives you a convenient way to document your code as you go along. If you aren’t a strong programmer or scripting guru (I’m not), you should use a lot of comments to help guide you through the script. This can help with troubleshooting later on down the line.
Next you see a few lines prefixed with dollar signs ($). These are variables. If you are familiar with variables, skip to the next paragraph. If not: Variables allow you place reusable objects into your scripts that can contain differing values. That way, you can assign values to a variable, and use that variable in a command that otherwise needs some specific string input, and then later update that value and do it all again if needed. Above, you can see that I’ve created a variable named $computer to hold the name of the computer I am connecting to in the get-service command. I’ve also created a variable named $string to hold the matching pattern for the where-object cmdlet that helps me do some regular expression matching against my results.
After the variables, you see the actual command. First is get-service, which you are already familiar with. Then comes where-object, which is essentially a WHERE clause (similar to T-SQL) allowing me to filter on some input. Notice that this line has a block of code as an input:
where-object {$_.DisplayName –match $string}
The dollar sign represents, in this case, a placeholder for each value being returned by the DisplayName object in the initial get-service result. The parameter, –match, then tells the cmdlet that we are searching for a specific string, and then we include our placeholder $string, which equates to “^SQL.*”, which is essentially a regular expression type match that gives us all processes with the letters SQL in them.
Finally, you see the cmdlet out-file, which simply tells the script where to send the results. This can literally be any path (that we have rights to). Notice that we’ve used the pipe (|) between each cmdlet; this is what passes the data (and control) to the next cmdlet. If we omit the pipe, the results are returned and the next cmdlet is executed without any “incoming” objects, which will usually cause an error.
Now save the script file, and try executing it from the command line. You should end up with a very simple text document that has the output we expect, which is a list of SQL services on the target machine.