Problem
I want to get a catalog of all the SQL Server services running on my servers. How might I do this? I don’t want to take a bunch of screenshots of SQL Server Configuration Manager or SQL Server Management Studio. Surely there’s a better way!
Solution
There are two ways that should work, one which uses a command-line program called sc and the other being a PowerShell cmdlet. Let’s start with the first option.
Using SC for a list of all SQL Server Services
The first warning I’ll give is you need to run sc from the command prompt. If you open up a standard PowerShell window and type sc, it won’t work. That’s because sc is an alias for the cmdlet set-content. Should you try and run sc like that, you’ll get something like the following.
Either you’ll need to start a Command Prompt or you’ll need to enter cmd, which puts you into a command shell, like so:
Note: The reason I point this is out is on Windows 2012 and higher, you’ll normally start with the Server Manager and one of your options on the taskbar is the PowerShell window. In those cases you should go the PowerShell route, but in this case you don’t.
As you can probably tell from the previous image, you can use sc to query the system you’re on or you can query a remote system, provided you have the proper permissions. The server parameter is optional. The parameter you want to use is query because you’re gathering information on the services. Because we are interested in finding out what services are running, not the state they are in (Running or Stopped, for instance), we’re going to specify the state to be all. So for a local server, we’d type:
sc query state= all
The problem with this is it’ll give us all the services, which is not what we want. The best way to shrink down the list is to use another command line utility, findstr, which functions as a RegEx match program. We want to match the string “sql” and we don’t care about the case. Therefore, we’ll need to use a flag, -i, for case insensitivity, and we’ll need to specify the string. Just looking at the findstr command, we have:
findstr -i sql
Of course, we need to put this together to pare down exactly what we want. The easiest way to handle this is to get the output from the sc command and to send it to findstr. We can do this using the pipe (|) character (typically above the backslash on the keyboard), just as would in PowerShell or Perl. The final command ends up being:
sc query state= all | findstr -i sql
You should get output like the following on your system if there are any SQL Server related services installed:
If you want more detail about a specific service, you can use the query option again with sc. Except instead of specifying the state as a parameter, simply add the name. For instance, if I wanted more details on the MSSQL$SQL2012 service, I could type the following:
sc query MSSQL$SQL2012
And this is what I’d get back. Just this query alone doesn’t provide a lot of information, but it’s useful if you’re just checking to see if a service is running, especially remotely.
Using PowerShell to find all SQL Server Services
If PowerShell is installed on the system, it’s the better option because there’s a cmdlet designed to return information on services, get-service. If we want to do the equivalent of what we did with sc, we can use the -name parameter and use *sql* to find the list of SQL Server related services:
get-service -name *sql*
We will get back a bit different output, though:
One of the things about the default output is that the names are cut off. This is where we can use the full capabilities of PowerShell to make things look better. For instance, we can pipe to format-table:
And like sc, get-service can connect to a remote system so long as you have the proper permissions. In that case you’d use the -ComputerName flag, just like with a lot of other cmdlets.
What Next?
At this point, we’ve got the output on the screen. With sc, the output can piped to a file like so:
sc query state= all | findstr -i sql > sometextfile.txt
We can do the same with PowerShell or we can use other options like the cmdlets out-file or export-csv. Then we can collect and process the file. Again, if you’re using PowerShell, with get-service and select-object we can also choose exactly what properties we want to expose. This is another reason to consider PowerShell first.
Other Options?
Yes, there are. If you have an enterprise management system like System Center, likely that system is already collecting the same information. It’s just a matter of writing the correct custom reports to get the information out. However, if you don’t have those sorts of products or you don’t have access to them, the command lines tools we get with Windows (to include PowerShell) provide enough power to catalog the SQL Server services (and really any service), so long as we have the proper permissions on the servers in question.
Next Steps
- Learn how to control SQL Server services using PowerShell.
- Read how to use PowerShell and WMI to pull even more information on SQL Server services.
- Check out this tip on sc – Start, Stop, Pause and Query Windows SQL Server Services Remotely.
- Check out all of the PowerShell tips.
