Creating PowerShell Scripts

Now that you know what power lies in PowerShell, let's write a simple script to get some info on some SQL services.

• By Joshua Jones
• 10/05/2009

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. Let's 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.

The root of its usefulness is the .NET Framework. Because PowerShell relies on the .NETFX, it has the ability to access and manipulate almost any object in Windows (via WMI), as well as other Microsoft products, such as SQL Server. This allows administrators and programmers a great deal of flexibility in creating automated processes from the command line.

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, it's always best to develop 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.

Okay, now let's set the policy, which is real easy:

Set-ExecutionPolicy RemoteSigned

Figure 1. Setting the policy with PowerShell.

Easy enough, right? So now that the environment is set up, it's 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 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:

1. Return a list of SQL related services running on a given machine.
2. Return a list of database (omitting the system databases).
3. 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 Fig. 2. But what about a script?

Figure 2. A list of SQL services running on a machine, from a simple command prompt. How about we write a script that does better than this?

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, the Run box, or Start menu. So, you can use the ISE if you'd like. If you'd rather not, you can 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.

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. Check out the script in Fig. 3.

Figure 3. Script for listing all SQL services running on a machine.

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. For the uninitiated: 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. Here, 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 is 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.

This is a very basic script; there's a lot more that can be done, which is what the rest of this series will entail. For now, look at creating some scripts of your own, and have fun!