Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

PowerSQL -Recycle SQL Instance (Remote or Local)

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)


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...