We have a scheduled maintenance window to recycle SQL SERVER Instance once in 30 days hence created a PowerShell job where function being placed along with parameters(servername and instancename) and executed it across all SQLinstance. (Named or Default instance) Default instance-> MSSQLSERVER and Named Instance ->MSSQL`$KAT ( To override the named instance use ` infront of $ sign [` Grave Accent])
The same function being called to start and stop the service.
PS:\>RESTART-SQLINSTANCE AQDB001 MSSQLSERVER
PS:\>RESTART-SQLINSTANCE AQDB001 MSSQL`$KAT
Code is given below
**************************************************************************************
FUNCTION RESTART-SQLINSTANCE
{
PARAM([STRING]$SERVERNAME,[STRING]$SERVICENAME)
#MSSQLSERVER ->DEFAULT INSTANCE and NAMED INSTANCE -> MSSQL`$KAT
$SERVICE = GET-SERVICE -COMPUTERNAME $SERVERNAME $SERVICENAME -ERRORACTION SILENTLYCONTINUE
IF( $SERVICE.STATUS -EQ “RUNNING” )
{
$DEPSERVICES = GET-SERVICE -COMPUTERNAME $SERVERNAME $SERVICE.NAME -DEPENDENTSERVICES | WHERE-OBJECT {$_.STATUS -EQ “RUNNING”}
IF( $DEPSERVICES -NE $NULL )
{
FOREACH($DEPSERVICE IN $DEPSERVICES)
{
Stop-Service -InputObject (Get-Service -ComputerName $SERVERNAME -Name $DEPSERVICE.NAME)
}
}
Stop-Service -InputObject (Get-Service -ComputerName $SERVERNAME -Name $SERVICE.NAME) -Force
}
ELSEIF ( $SERVICE.STATUS -EQ “STOPPED” )
{
Start-Service -InputObject (Get-Service -ComputerName $SERVERNAME -Name $SERVICE.NAME)
$DEPSERVICES = GET-SERVICE -COMPUTERNAME $SERVERNAME $SERVICE.NAME -DEPENDENTSERVICES | WHERE-OBJECT {$_.STATUS -EQ “STOPPED”}
IF( $DEPSERVICES -NE $NULL )
{
FOREACH($DEPSERVICE IN $DEPSERVICES)
{
Start-Service -InputObject (Get-Service -ComputerName $SERVERNAME -Name $DEPSERVICE.NAME)
}
}
}
ELSE
{
WRITE-OUTPUT “THE SERVER AND SERVICE DOES NOT EXIST”
}
}
*************************************
Output:
PS P:\> RESTART-SQLINSTANCE AQDB001 MSSQL`$KAT123
THE SPECIFIED SERVER AND SERVICE DOES NOT EXIST
RUN 1:
PS P:\> RESTART-SQLINSTANCE AQDB001 MSSQL`$KAT
PS P:\> get-service *| where {$_.name -like ‘*KAT’}
Status Name DisplayName
—— —- ———–
Stopped MSSQL$KAT SQL Server (KAT)
Stopped SQLAgent$KAT SQL Server Agent (KAT)
RUN 2:
PS P:\> RESTART-SQLINSTANCE AQDB001 MSSQL`$KAT
PS P:\> get-service *| where {$_.name -like ‘*KAT’}
Status Name DisplayName
—— —- ———–
Running MSSQL$KAT SQL Server (KAT)
Running SQLAgent$KAT SQL Server Agent (KAT)
