In my last blog post, I discussed how to get PowerShell and SQLPS up and running on your machine(s). And much like my dog chasing its tail, you’re probably asking yourself, “Now that I have it, what do I do with it?”
The first thing you’ll want to learn is how to actually connect to, and get information about, your servers. So, go ahead and go to your Start>Run or Start>Search box and type SQLPS. If everything is installed correctly, you should see:
Alright, let’s start off with looking at the SQL Server services running on a given machine. Use the cmdlet “get-service” to find out about services on either your local or a remote machine. This is not a SQLPS specific command, but rather a PowerShell command:
get-service –computername CSSRVSQLTST –displayname *SQL*
Not bad. We passed in a few parameters: –computername tells the command what computer to connect to (the default is localhost), and –displayname helps us filter on the name of the service (with the help of the asterisk as a wildcard). This corresponds to the displayed name in the Services control panel. If you want to look for the actual service name, use –name. If you want to get fancy, and see how cmdlets can interact, we can sort the output of the query:
get-service –computername CSSRVSQLTST –displayname *SQL* | sort-object status
If you want to find out what else you can do with get-service, just type get-help get-service –examples.
Now, what about actually executing some T-SQL? Most DBAs are already familiar with SQLCMD, introduced with SQL Server 2005 to replace the old OSQL command line query interface. SQLPS uses a cmdlet called invoke-sqlcmd to call SQLCMD and pass a query to it. It supports both T-SQL and XQuery and a variety of parameters. Here’s a simple query:
invoke-sqlcmd -ServerInstance CSSRVSQLTST -Database msdb –Query "Select top 2 * from sys.objects"
So you should take two things away from that screenshot: 1. It’s an easy command, and 2. That is not a great way to get data back if you’re going to do anything with it. If you’re going to be executing a stored procedure, or querying for one or two very specific rows from a table/view, this method is fine, and very easy. However, if you’re looking at a bunch of rows coming back, there’s a better way. PowerShell provides some cmdlets that you can pipe the result set to in order to get a better looking format. For example, you could export the results to a CSV file:
invoke-sqlcmd -ServerInstance CSSRVSQLTST -Database msdb –Query "Select top 2 * from sys.objects" | Export-Csv –path C:\MyFiles\results.csv
When run, this command returns no output to the command line screen, but dumps the file to the specified directory. Using this method will get you a slightly more usable list of rows to review. Again, use get-help invoke-sqlcmd to get more info.
At this point, you have enough information to be dangerous! Be sure you’re careful when querying any production servers, because if you have the right credentials, you could do a lot of damage. In the next post, I’ll talk about how to create scripts using basic PowerShell and SQLPS cmdlets that access system objects in SMO and Windows. Until then, have fun!
Pingback from Dew Drop – September 18, 2009 | Alvin Ashcraft's Morning Dew
Nice intro. Definitely would like to see more practical things you have done, or see yourself doing with PS.
Running SQL 2008 with the version of powershell installed with it there appears to be no way of pulling the services on a remote machine using get-service
I've done this before using WMI queries, however it's not a clean way to do it and it can hang on certain machines and never complete. Get-service seems ideal, however that remote limitation is a concern.
Nicholas,
Remote machine connection is a new feature in PS 2.0. If you haven't upgraded yet, you will be constrained on the local machine.
I must be missing something. Why would anyone want to spend the time typing in roughly 180 characters (mistake free) in a DOS propmpt when SS Management Studio with Intellisense is so much faster, easier, friendlier, prettier, more flexible (e.g. copy/paste), more reliable, etc? Using Power Shell is like taking a trip back in time ... especially for fat fingered typists like me.
It is a nicely written article, nevertheless.
The power of PowerShell is not typing this interactively, but scripting it for later, and repeated, execution.
Like Steve said, its the scripting ability (which is the topic of the next post, if I could just get it finished). In fact, I've got several planned to look at managing users, backups/restores, and policies via PowerShell.
Good article. I decide to write a Powershell article and suddenly all the bloggers are doing it!
I've always found that knowledge of scripting, vbscript mainly, has always been a big help and allows me to quickly get to grips at a new client. Converting all of these to PS will be a big task.
I haven't looked at PS2.0 yet, but with PS1.0 it makes sense to use the main PowerShell command instead of SQLPS. It is less restrictive; SQLPS won't allow scripts to be run.
Having said that, I rarely run scripts (.ps1 files) as I prefer to encapsulate them in functions and add them to my PS profile (Microsoft.PowerShell_Profile.ps1). This is a much more convenient way of using them.
Thanks Richard. PowerShell does seem to be coming in to its own all of the sudden! Seems like a lot of DBAs are still just figuring it out though; the more of us writing about it the better.
