My First Steps in PowerShell

I (finally) got round to spending a few minutes playing with PowerShell today.  It’s been on my “To Do” list for about 18 months now, which is rather embarrassing.  Still.  First steps, and all that.

My laptop was running slowly, and I wanted to stop all SQL Server services (I don’t use them very often these days – only for helping out on AskSSC). I couldn’t face running through the Services control panel killing them one by one, so I started poking around in PowerShell.

Get-Service

The Get-Service cmdlet seemed to be the ideal place to start. This returns a list of all services, by default providing their Status (Running, Stopped, etc), internal name, and DisplayName (the name you see in the Services list that you get when you do Start->Run->Services.msc). A useful starting point, but my laptop has way more than just SQL Server services on it (as does every Windows PC).

Where-Object

So we want to filter that list to only show SQL-related services. This is where the Where-Object comes in. This takes the output from the previous commandlet, and removes items that don’t match the parameters you provide. In my example, I’m doing:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*"}

Note that the wildcard used by “like” is the *, not the % to which I, a long-time SQLer, am accustomed.

Now, we want to pass that to a service-killer routine. But there’s no point in getting it to kill services that aren’t running, so we first need to add another filter:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*" -and $_.status -eq "Running"}

Yes, indeed. Boolean and/or/not/equal operators are all there, but disguised. Use -eq rather than =, for example.

So, we have a list of running SQL-related services. Where’s that killer?

Stop-Service

The Stop-Service cmdlet does what it says on the tin. Given that we’re passing in a list of Services to be stopped, there’s no need for us to do anything more than:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*" -and $_.status -eq "Running"} |
         Stop-Service

Yay!

Caveats

OK, the script as provided above does have a couple of minor issues, the worst of which is that it has no concept of the service dependencies – it’ll try to stop the SQL Agent service before stopping the SQL Server service, for example. I’ll work on this for v2. Error handling is also missing… I suppose I could alter the script to first kill any service that’s like “*SQL*” but the internal name isn’t like “MSSQL$*”:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*" -and -not($_.name -like "MSSQL$*")}

Hmm. Is it possible to use the Sort-Object script to do this? Y’know, like you would with SQL Server:

ORDER BY CASE WHEN Name LIKE 'MSSQL$%' THEN 1 ELSE 0 END, DisplayName

$_.

What’s this $_. bit? That’s a short-hand. The $_ represents the current object being worked on by the command, and the . is an indicator to get the named property of that object.

It might help to remember that this is an object-oriented environment – cmdlets / commandlets / commands act on objects (or sets of objects), and so each object has properties associated with it (“members” in PowerShell-ese).

Get-Member

Last bit for today Get-Member cmdlet, which returns a list of properties and methods associated with the object piped into it. For example, for a service, we could do:

Get-Service "SQL Server Browser" |
         Get-Member

PoSh Get-Member sample output

Disclaimer

Please note, this is my first time playing with PowerShell. I’m sure there are better ways of doing all of this, including using a more sensible development environment than just the command line (launch a command window, and then run “PowerShell” within it… The “PS” prompt tells you that you’re not in Kansas any more…)

About these ads
This entry was posted in SQLServerPedia Syndication and tagged . Bookmark the permalink.

2 Responses to My First Steps in PowerShell

  1. David Scutt says:

    You can launch Powershell from within SQL Server Management Studio – SQL Server comes with its own set of libraries and cmdlets that let you work with SQL Server more directly – so you could connect to an Instance and use Get-Childitem to get a list of databases, rather than a list of files, for example…

  2. Pingback: Services and Sorts in PowerShell | The Lone DBA

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