Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Catalog SQL Server Services Installed on Servers


By:   |   Last Updated: 2014-12-17   |   Comments (3)   |   Related Tips: More > SQL Server Configurations

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.

Cataloging SQL Server services on servers

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:

Either you'll need to start a Command Prompt or you'll need to enter cmd

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:

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.

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:

Using PowerShell

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:

One of the things about the default output is that the names are cut off.

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


Last Updated: 2014-12-17


next webcast button


next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 17, 2014 - 4:00:05 PM - WMI also works Back To Top
Another item for those who like the WMIC solution; here's a bcp format file to import the results if you add the /FORMAT:tsv.xsl option at the end, with the contents of tsv.xsl being: Node {;} "" \\\: .="" fmt file: 10.0 26 1 SQLCHAR 0 260 "\t" 1 Node SQL_Latin1_General_Pref_CP1_CI_AS 2 SQLCHAR 0 5 "\t" 2 AcceptPause SQL_Latin1_General_Pref_CP1_CI_AS 3 SQLCHAR 0 5 "\t" 3 AcceptStop SQL_Latin1_General_Pref_CP1_CI_AS 4 SQLCHAR 0 512 "\t" 4 Caption SQL_Latin1_General_Pref_CP1_CI_AS 5 SQLCHAR 0 21 "\t" 5 CheckPoint "" 6 SQLCHAR 0 256 "\t" 6 CreationClassName SQL_Latin1_General_Pref_CP1_CI_AS 7 SQLCHAR 0 1024 "\t" 7 Description SQL_Latin1_General_Pref_CP1_CI_AS 8 SQLCHAR 0 5 "\t" 8 DesktopInteract SQL_Latin1_General_Pref_CP1_CI_AS 9 SQLCHAR 0 256 "\t" 9 DisplayName SQL_Latin1_General_Pref_CP1_CI_AS 10 SQLCHAR 0 32 "\t" 10 ErrorControl SQL_Latin1_General_Pref_CP1_CI_AS 11 SQLCHAR 0 21 "\t" 11 ExitCode "" 12 SQLCHAR 0 32 "\t" 12 InstallDate SQL_Latin1_General_Pref_CP1_CI_AS 13 SQLCHAR 0 64 "\t" 13 Name SQL_Latin1_General_Pref_CP1_CI_AS 14 SQLCHAR 0 512 "\t" 14 PathName SQL_Latin1_General_Pref_CP1_CI_AS 15 SQLCHAR 0 21 "\t" 15 ProcessId "" 16 SQLCHAR 0 21 "\t" 16 ServiceSpecificExitCode "" 17 SQLCHAR 0 32 "\t" 17 ServiceType SQL_Latin1_General_Pref_CP1_CI_AS 18 SQLCHAR 0 5 "\t" 18 Started SQL_Latin1_General_Pref_CP1_CI_AS 19 SQLCHAR 0 16 "\t" 19 StartMode SQL_Latin1_General_Pref_CP1_CI_AS 20 SQLCHAR 0 256 "\t" 20 StartName SQL_Latin1_General_Pref_CP1_CI_AS 21 SQLCHAR 0 32 "\t" 21 State SQL_Latin1_General_Pref_CP1_CI_AS 22 SQLCHAR 0 16 "\t" 22 Status SQL_Latin1_General_Pref_CP1_CI_AS 23 SQLCHAR 0 64 "\t" 23 SystemCreationClassName SQL_Latin1_General_Pref_CP1_CI_AS 24 SQLCHAR 0 260 "\t" 24 SystemName SQL_Latin1_General_Pref_CP1_CI_AS 25 SQLCHAR 0 21 "\t" 25 TagId "" 26 SQLCHAR 0 21 "\r\n" 26 WaitHint ""

Wednesday, December 17, 2014 - 3:31:12 PM - WMI also works Back To Top
Other easy options include WMI, i.e. wmic /node:"YourMachine" service or with filtering: wmic /node:"YourMachine" service where (caption like "%sql%") or with only the data you need (StartName is the account whose credentials the service is running under) and filtering wmic /node:"YourMachine" service where (caption like "%sql%") get caption, PathName, Started, StartMode, StartName

Wednesday, December 17, 2014 - 8:18:16 AM - Steve LaMotte Back To Top

Great information, I track all the SQL servers at our production plants, mostly remotely.  This will save me a few hours a week.

Thank you,

Steve LaMotte


Learn more about SQL Server tools