Saturday, October 15, 2016

Can you start and stop SQL Server Instance using PowerShell?

The short answer is yes.

Here is the long answer which was verified on my Windows 10 Computer which has installed on it three versions of SQL Servers.

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 (to open Services, just type Services in the search charm).

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). Enter the following code.

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 highlighted in the above code.

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.


No comments: