Search code examples
sql-serverpowershellserviceparameterssql-agent

How to add a parameter to this PowerShell script to stop the named SQL Server service?

While using PowerShell to stop some SQL Server services:

Get-Service  -Name 'MSSQLSERVER' | Stop-Service -Force -Verbose
Get-Service  -Name 'SQLSERVERAGENT' | Stop-Service -Force -Verbose
Get-Service  -Name 'SQLBrowser' | Stop-Service -Force -Verbose
Get-Service  -Name 'SQLTELEMETRY' | Stop-Service -Force -Verbose
Get-Service  -Name 'SQLWriter' | Stop-Service -Force -Verbose

I realised that on this particular server, it is a named instance that I have

enter image description here

How would I find out and add the instance name to my scripts above? So that I can stop the relevant services of this particular instance?

Basically this worked, but how can I automate it?

Get-Service  -Name 'MSSQL$CELCAT' | Stop-Service -Force -Verbose
Get-Service  -Name 'SQLAgent$CELCAT' | Stop-Service -Force -Verbose
Get-Service  -Name 'SQLBrowser' | Stop-Service -Force -Verbose
Get-Service  -Name 'SQLTELEMETRY$CELCAT' | Stop-Service -Force -Verbose
Get-Service  -Name 'SQLWriter' | Stop-Service -Force -Verbose

enter image description here

Solution

  • There are a few options. One is to query instance names and build the stop commands based on those. This is a bit tricky, since the default instance name is handled in different a way from the others. That is, the service name is MSSQLSERVER, but the instance name is default.

    To get a list of instances, query SQLSERVER: provider like so,

    Import-Module -Name sqlpssqlps 
$inames = Get-ChildItem -Path "SQLSERVER:\SQL\$env:computername" | select instancename
foreach($instance in $inames) { get-service -name $instance | stop-service }

    Another is to simply list services with wildcards like so,

    # Match MsSqlServer and MsSql<instance>
Get-Service  -Name MSSQL* | % {stop-serivce $_ -force}
# Match SqlServerAgent and SqlAgent<instance>
Get-Service  -Name SQL*Agent* | % {stop-serivce $_ -force}