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)

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s