Wednesday, October 5, 2016

Starting and stopping a SQL Server instance using PowerShell

Firstly let us see the status of the SQL Server 2016 on this computer. There are three SQL Servers 2012, 2014 and 2016. Let us take a look at the SQL Server named instance OHANA. It appears to be stopped as seen in the Services window of the Control Panel.


StoppedOHana

Keep the Services screen open.

It is possible to start the SQL Server (OHANA) from the Services screen, but we want to start it using PowerShell.

Launch Windows PowerShelll ISE with Administrator Permission. Type in Start-Service to see the intellisense drop-down as shown.


StartService_00.jpg

Access the parameters available by just typing a hyphen (-) and waiting,


StartService_01.jpg

Pick the DisplayName parameter.

Now you need to provide a display name for SQL Server (OHANA).

Start-Service -DisplayName 'SQL Server (OHANA) '

Run the code or hit F8.

This will produce an error as shown:
PS C:\WINDOWS\system32> start-Service -DisplayName 'SQL Server (OHANA) 'start-Service : Cannot find any service with display name 'SQL Server (OHANA)
'.
At line:1 char:1
+ start-Service -DisplayName 'SQL Server (OHANA) '
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (SQL Server (OHANA) :String) [St
   art-Service], ServiceCommandException
    + FullyQualifiedErrorId : NoServiceFoundForGivenDisplayName,Microsoft.Powe
   rShell.Commands.StartServiceCommand


You notice that there is an extra space between (OHANA) and the single quote.

Now, remove that white space and hit F8.

You get the following response:


StartService_02.jpg

In the ControlPanel |..|Services window click Action | Refresh.

You will notice that the SQL Server (OHANA) has started.

You can also stop the SQL Server with the following statement:

PS C:\WINDOWS\system32> Stop-Service -DisplayName  'SQL Server (OHANA)'

WARNING: Waiting for service 'SQL Server (OHANA) (MSSQL$OHANA)' to stop...

Refresh the Services windows and verify that the server has stopped.