Manage SQL Server services with Powershell

Standard

I tend to use SQL Server on my laptop to test out new features and develop and test scripts. I’ve tried creating a VM for this but everything slows down too much and I keeps me from finishing stuff. I have the patience of a humming bird so waiting for a few minutes feels like a lifetime.

When you install SQL Server you get the choice to set up the way the SQL Server services are started. You can select automatic, manual, disabled etc.
The services on my laptop are set on manual because I don’t use SQL Server all the time.

The downside is that I either have to go to the services of Windows or execute a command from a command prompt and keep in mind that it is in administrator mode.

It’s possible to start and stop the SQL Server services with powershell so that’s going to be my solution.

One of the things you want the script to do is check if the window where it’s started, is in elevated (administrator) mode. If it’s not you can’t start the services. Benjamin Armstrong made a nice self-elevating script that will check the elevation and restart the script in elevated mode to make sure the script is executed succesfully.

Here is the code for self elevating the window:

# Get the ID and security principal of the current user account
$myWindowsID=[System.Security.Principal.WindowsIdentity]::GetCurrent()
$myWindowsPrincipal=new-object System.Security.Principal.WindowsPrincipal($myWindowsID)
 
# Get the security principal for the Administrator role
$adminRole=[System.Security.Principal.WindowsBuiltInRole]::Administrator
 
# Check to see if we are currently running "as Administrator"
if ($myWindowsPrincipal.IsInRole($adminRole))
   {
   # We are running "as Administrator" - so change the title and background color to indicate this
   $Host.UI.RawUI.WindowTitle = $myInvocation.MyCommand.Definition + "(Elevated)"
   $Host.UI.RawUI.BackgroundColor = "DarkBlue"
   clear-host
   }
else
   {
   # We are not running "as Administrator" - so relaunch as administrator
   
   # Create a new process object that starts PowerShell
   $newProcess = new-object System.Diagnostics.ProcessStartInfo "PowerShell";
   
   # Specify the current script path and name as a parameter
   $newProcess.Arguments = $myInvocation.MyCommand.Definition;
   
   # Indicate that the process should be elevated
   $newProcess.Verb = "runas";
   
   # Start the new process
   [System.Diagnostics.Process]::Start($newProcess);
   
   # Exit from the current, unelevated, process
   exit
 }

The next part is easier where we have to create a managed computer object.

# Load the neccesary assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
 
#Create a new Managed computer object for the instance
$Wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer

Next up is get the services into variables:

# Run your code that needs to be elevated here
$SQLService = $Wmi.Services['MSSQLSERVER']
$SQLAgent = $Wmi.Services['SQLSERVERAGENT']

At last we start the services by checking of they are in the right state and execute the Start() or Stop() command. I only included the SQL Service and the SQL Agent but you can easily add the other services of SQL Server.

For starting the services:

# Check if the service is in the right state
if($SQLService.ServiceState -eq 'Stopped')
{
    Write-Host "Starting SQL Server service..." -ForegroundColor Green

    # Start the SQL service.
    #$SQLService.Start();

} 
else
{
    Write-Host 'SQL Server service is already started' -ForegroundColor Red
}

# Check if the service is in the right state
if($SQLAgent.ServiceState -eq 'Stopped')
{
    Write-Host "Starting SQL Server agent..." -ForegroundColor Green

    #Start the SQL agent
    $SQLAgent.Start()
} 
else
{
    Write-Host 'SQL Server agent is already started' -ForegroundColor Red
}

For stopping the services:

if($SQLAgent.ServiceState -eq 'Running')
{
    Write-Host "Stopping SQL Server agent..." -ForegroundColor Green

    #Start the SQL agent
    $SQLAgent.Stop();
} 
else
{
    Write-Host 'SQL Server agent is already stopped' -ForegroundColor Red
}

if($SQLService.ServiceState -eq 'Running')
{
    Write-Host "Stopping SQL Server service..." -ForegroundColor Green

    # Start the SQL service.
    $SQLService.Stop();
} 
else
{
    Write-Host 'SQL Server service is already stopped' -ForegroundColor Red
}

To dot the i’s I included a command to wait for a keystroke to close the window:

Write-Host "Press any key to continue ..."

$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

The final result can be seen below:

Start-SQLServer.ps1

# Get the ID and security principal of the current user account
$myWindowsID=[System.Security.Principal.WindowsIdentity]::GetCurrent()
$myWindowsPrincipal=new-object System.Security.Principal.WindowsPrincipal($myWindowsID)
 
# Get the security principal for the Administrator role
$adminRole=[System.Security.Principal.WindowsBuiltInRole]::Administrator
 
# Check to see if we are currently running "as Administrator"
if ($myWindowsPrincipal.IsInRole($adminRole))
   {
   # We are running "as Administrator" - so change the title and background color to indicate this
   $Host.UI.RawUI.WindowTitle = $myInvocation.MyCommand.Definition + "(Elevated)"
   $Host.UI.RawUI.BackgroundColor = "DarkBlue"
   clear-host
   }
else
   {
   # We are not running "as Administrator" - so relaunch as administrator
   
   # Create a new process object that starts PowerShell
   $newProcess = new-object System.Diagnostics.ProcessStartInfo "PowerShell";
   
   # Specify the current script path and name as a parameter
   $newProcess.Arguments = $myInvocation.MyCommand.Definition;
   
   # Indicate that the process should be elevated
   $newProcess.Verb = "runas";
   
   # Start the new process
   [System.Diagnostics.Process]::Start($newProcess);
   
   # Exit from the current, unelevated, process
   exit
 }

# Load the neccesary assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
 
#Create a new Managed computer object for the instance
$Wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
 
# Run your code that needs to be elevated here
$SQLService = $Wmi.Services['MSSQLSERVER']
$SQLAgent = $Wmi.Services['SQLSERVERAGENT']

# Check if the service is in the right state
if($SQLService.ServiceState -eq 'Stopped')
{
    Write-Host "Starting SQL Server service..." -ForegroundColor Green

    # Start the SQL service.
    #$SQLService.Start();

} 
else
{
    Write-Host 'SQL Server service already started' -ForegroundColor Red
}

# Check if the service is in the right state
if($SQLAgent.ServiceState -eq 'Stopped')
{
    Write-Host "Starting SQL Server agent..." -ForegroundColor Green

    #Start the SQL agent
    $SQLAgent.Start()
} 
else
{
    Write-Host 'SQL Server agent already started' -ForegroundColor Red
}

Write-Host "Press any key to continue ..."

$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

Stop-SQLServer.ps1

# Get the ID and security principal of the current user account
$myWindowsID=[System.Security.Principal.WindowsIdentity]::GetCurrent()
$myWindowsPrincipal=new-object System.Security.Principal.WindowsPrincipal($myWindowsID)
 
# Get the security principal for the Administrator role
$adminRole=[System.Security.Principal.WindowsBuiltInRole]::Administrator
 
# Check to see if we are currently running "as Administrator"
if ($myWindowsPrincipal.IsInRole($adminRole))
   {
   # We are running "as Administrator" - so change the title and background color to indicate this
   $Host.UI.RawUI.WindowTitle = $myInvocation.MyCommand.Definition + "(Elevated)"
   $Host.UI.RawUI.BackgroundColor = "DarkBlue"
   clear-host
   }
else
   {
   # We are not running "as Administrator" - so relaunch as administrator
   
   # Create a new process object that starts PowerShell
   $newProcess = new-object System.Diagnostics.ProcessStartInfo "PowerShell";
   
   # Specify the current script path and name as a parameter
   $newProcess.Arguments = $myInvocation.MyCommand.Definition;
   
   # Indicate that the process should be elevated
   $newProcess.Verb = "runas";
   
   # Start the new process
   [System.Diagnostics.Process]::Start($newProcess);
   
   # Exit from the current, unelevated, process
   exit
 }


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
 
#Create a new Managed computer object for the instance
$Wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
 
# Run your code that needs to be elevated here
$SQLService = $Wmi.Services['MSSQLSERVER']
$SQLAgent = $Wmi.Services['SQLSERVERAGENT']


if($SQLAgent.ServiceState -eq 'Running')
{
    Write-Host "Stopping SQL Server agent..." -ForegroundColor Green

    #Start the SQL agent
    $SQLAgent.Stop();
} 
else
{
    Write-Host 'SQL Server agent already stopped' -ForegroundColor Red
}

if($SQLService.ServiceState -eq 'Running')
{
    Write-Host "Stopping SQL Server service..." -ForegroundColor Green

    # Start the SQL service.
    $SQLService.Stop();
} 
else
{
    Write-Host 'SQL Server service already stopped' -ForegroundColor Red
}

Write-Host "Press any key to continue ..."

$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

This all made starting and stopping my SQL Server services a lot easier. Hope you find this useful and any comment is appreciated.

Leave a Reply

Your email address will not be published.