Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Controlling Windows and SQL Server Services using Windows PowerShell


By:   |   Read Comments (6)   |   Related Tips: More > PowerShell


SQL Server Conference Giveaway - click to learn more


Problem

I discussed "How to access event logs using PowerShell" in my last tip. Here I will introduce a few PowerShell cmdlets related to accessing and handling Windows and SQL Server services. In this tip I will explain how to access services using Windows PowerShell cmdlets.

Solution

Windows services are programs that run in the background to perform specific functions. Many services start when the Windows operating system is booted to perform their operation. We normally manage Windows services by using the service management console, which can be launched by selecting Start > Run > and typing services.msc. Once you press enter a service management console will appear with a list of services on your server. This is the place from where you generally manage Windows services.

NOTE:-Always run PowerShell prompt in "Run As Administrator" mode for administrative tasks.

Getting a List of Windows Services

Get-Service: We can use this cmdlet to lists all Windows services on your local system or remote system.

--List all windows services on local machine.
Get-service

--list all windows services of a remote computer. Get-Service -computername SERVERNAME

Filtering Windows or SQL Server services

We can apply filters as well to display a specific service. Suppose we want to see all services whose name begin with "SQL" or a specific service like MSSQLSERVER. We can use the -displayname parameter to display all services whose display name contains "SQL" And we will use -Name parameter to get a specific service like MSSQLSERVER.

--Display all services whose name contains SQL.
Get-service -displayname "*SQL*"

--Display a specific service. Get-Service -Name MSSQLSERVER

Apply filter to get specific services.

We can also filter the output of these cmdlets. We can use another cmdlet where-object to filter any logs. Suppose we want to check all SQL server services which are in running state. We can add both cmdlets with the help of a pipe and filter as shown below. The output of the first cmdlet (before pipe) is saved in $ and we can use $ to access the output.

--Display all services whose status is in running state.
Get-service | Where-Object {$_.status -eq "Running"}

--Display all SQL Server services which are in running state. Get-service -displayname *SQL* | Where-Object {$_.status -eq "Running"}
--Display all SQL Server Services which are in stopped state. Get-service -displayname *SQL* | Where-Object {$_.status -eq "stopped"}

AllSQL Services which is in running state.

As we can see in the above screenshot, we can get all services which are in stopped state in the same way. I did not include a screenshot for the first set of code because the list was too large.

Starting and Stopping Services

To stop a service we use the Stop-Service cmdlet.

Suppose we want to stop one of the SQL Server services. First run get-service to list SQL services to check the status then stop it as shown below. If you want to shutdown all SQL Server services then we can also do this with the help of filters.

--Check the status of a SQL Server service.
Get-Service -Name SQLserveragent

--Stop above service. Stop-Service -Name Sqlserveragent
--Stop all SQL Server services in one go. Stop-Service -displayname *SQL* -force

Stop Services.

Sometimes a few services will fail to stop by normal Stop-service cmdlet with the below error.

Stop-Service : Cannot stop service 'SQL Server (MANVENDRA) (MSSQL$MANVENDRA)' because it has dependent services. It can
 only be stopped if the Force flag is set.
At line:1 char:13
+ stop-service <<<<  -displayname *sql*
    + CategoryInfo          : InvalidOperation: (System.ServiceProcess.ServiceController:ServiceController) [Stop-Serv
   ice], ServiceCommandException
    + FullyQualifiedErrorId : ServiceHasDependentServices,Microsoft.PowerShell.Commands.StopServiceCommand
 

This means one or more services are dependent on this service, so to avoid this issue either we can stop all dependent services or we can use the -FORCE parameter along with Stop-Service as shown in the above screenshot.

Stopping services could create an issue, so to be safe we can add a few parameters to get a confirmation screen about what we will be performing.

Here we will add two parameters. The first is -whatif and second is -confirm. If you use -whatif it will display a preview about what you are going to do and if you are use -confirm it will ask you again to choose Y for yes and N for no to proceed. The below screenshot shows how both parameters work.

--Running cmdlet with -whatif parameter.
Stop-Service -displayname *SQL* -whatif

--Stop all SQL Server services which are in a running state with -confirm. Get-service -displayname *SQL* | Where-object {$_.status -eq "running"} |Stop-Service -confirm

Stop services with whatif and confirm switche parameters

To start a service we use the Start-Service cmdlet.

Starting a service is the same as stopping a service. Just use Start-Service in place of Stop-Service and run with all parameters which we have used above.

Below are a few cmdlets which start a service.

--Start the service.
Start-service -Name SQLSERVERAGENT

--Check the status of the started service. Get-Service -Name SQLSERVERAGENT
--Get a preview of Starting all SQL Server Services which is not started. Get-Service -displayname *SQL* | where-object {$_.Status -eq "Stopped"} |start-service -whatif
--Starting all SQL Server Services which are not started. Get-Service -displayname *SQL* | where-object {$_.Status -eq "Stopped"} |start-service -confirm
--Check the status. Get-service -displayname *SQL*

I am not attaching screenshots since this is very similar to the stop-service cmdlet which were shown in the above screenshots.

We can also restart a services with the help of PowerShell cmdlets.

--Restart the services.
restart-service -name sqlserveragent

--Restart all SQL Server Services. restart-service -displayname *SQL* -Force

Changing Configurations of Windows/SQL Server services

To change a configuration we can use Set-service.

This cmdlet will change the configuration of a service like startup types, display name, etc...

--Check the startup type of SQL Server Agent service.
 get-wmiobject win32_service | where {$_.Name -eq "sqlserveragent"}

--Change the startup type of SQL Server agent service to Automatic. set-service sqlserveragent -startup automatic

You can again check whether the configuration is changed or not. See the below screenshot of the outputs for the above cmdlets.

CHange the startup type and verify it

Below are few more cmdlets which can be used to alter the configuration of any service. You can change the display name of a service, you can change the description of a service or you can start or stop a service with the set-service cmdlet.

--Change the display name of SQL Server Agent service (use same method for any windows service).
set-service -name sqlserveragent -DisplayName "Microsoft Sql Server Agent"

--Change the description of SQL Server agent service. set-service -name Sqlserveragent -description "SQL Server Agent Service to handle agent related tasks."
--You can also start or pause the services with the help of this cmdlet. --Suppose a SQL Server Agent service is stopped, we can start it by running. set-service sqlserveragent -status running
Next Steps
  • Try to use PowerShell cmdlets in your day to day activities. PowerShell is becoming more popular for SQL Server support on Windows core edition.
  • Read more tips on PowerShell.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, December 08, 2015 - 1:25:33 PM - Priyantha Perera Back To Top

I have several DB's tat are published to other location via transactional replication....

 

I need to be able to Stop and start the replication when we have to do a DB update. I need to do it through Powershell.. 


Friday, September 11, 2015 - 3:39:32 PM - M ZUBAIR Back To Top

These tips are very helpful for a man like me is new to SQL. Thanks to your team for providing these help topics for learning....God bless you.

 


Monday, September 01, 2014 - 4:14:46 AM - Bhavdip Back To Top

suppose i want to add two condition then some thing like below :

Get-service -displayname *SQL* | Where-Object {$_.status -eq "Running"} and {$_.Name -eq "MSSQLSERVER"}  


Tuesday, January 28, 2014 - 1:07:47 PM - Ray Back To Top

I have been trying to determine the Login Account for services.  How does one find that information?


Thursday, December 12, 2013 - 2:05:48 AM - Hitesh Shah Back To Top

Very good explanation.

But I have a problem in accessing the SQL Services. The power shell in administrator mode does not start or stop SQL database engine service as the name has '$' character due to instance specific service. Is there a way to escape the '$' character from the service name in cmdlet?

Thanks & Regards,

Hitesh Shah


Wednesday, May 29, 2013 - 1:42:46 AM - Gopalakrishnan Back To Top

Excellent one with good example and explanation. As always best of you.

 

Regards,

Gopal


Learn more about SQL Server tools