# This will be executed as the following# ECHO "BASE64STRING==" | .\script.ps1; Clear-History# Note1: Avoid using net commands to stop and start again the server# net stop MSSQLSERVER && net start MSSQLSERVER# sc stop MSSQLSERVER && sc start MSSQLSERVER# These will not automatically start dependencies like# SQL Server Agent service and SQL Server Browser service# Note2: Powershell Restart-Service cmdlet handles all dependencies but runs on Windows only# powershell -command "Restart-Service MSSQLSERVER -Force"# After MSSQLSERVER starts - cmdlet starts all previously stopped dependencies.# On Linux SQL Server comes with systemd unit files and can be controlled through systemctl# sudo systemctl restart mssql-server# sudo systemctl status mssql-server# Note3: Make sure you are running commands as admin# Make sure to restart all SQL Server named instances if required# When running on a cluster, the SQL Server Database Engine service# is best managed by using Cluster Administratorbegin {}process {# Stop if error. If not set, result value will be True and assumed there# was no problems. $ErrorActionPreference ="Stop"# Executes once for each pipeline object $JSON = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($_)) $Params=($JSON |ConvertFrom-Json)Write-Output"providerRecordUid=$($Params.providerRecordUid)"Write-Output"resourceRecordUid=$($Params.resourceRecordUid)"Write-Output"userRecordUid=$($Params.userRecordUid)"Write-Output"newPassword=$($Params.newPassword)"Write-Output"oldPassword=$($Params.oldPassword)"Write-Output"user=$($Params.user)"# SQL Server Instances that will be restarted (in list order) [string[]]$Instances ='MSSQLSERVER','MSSQL$instance1','MSSQL$instance2'# Restart service(s) on remote machine# To connect to a remote computer, you must be a member of the Administrators group# on the remote computer. To start an interactive session on the local computer,# you must start PowerShell with the Run as administrator option.# Note: In newer versions of PowerShell -ComputerName was removed from *-Service cmdlets# ex. Get-Service MSSQLSERVER -ComputerName MyServer | Restart-Service - no longer works# Note: Enter-PSSession shouldn't be used in scripts (runs script on localhost)# Prepare PSCredential parameter [string]$userName ='MyUserName' [string]$userPass ='MySuperSecureToken' [securestring]$secStringPass =ConvertTo-SecureString $userPass -AsPlainText -Force [pscredential]$credObject =New-Object System.Management.Automation.PSCredential ($userName, $secStringPass) [string]$hostname ='Server01'foreach ($Instance in $Instances) {Invoke-Command-ComputerName $hostname -Credential $credObject -ScriptBlock { Restart-Service-Name $Using:Instance -Force } }}end {}
Restarting Local SQL server
# This will be executed as the following# ECHO "BASE64STRING==" | .\script.ps1; Clear-History# Note1: Avoid using net commands to stop and start again the server# net stop MSSQLSERVER && net start MSSQLSERVER# sc stop MSSQLSERVER && sc start MSSQLSERVER# These will not automatically start dependencies like# SQL Server Agent service and SQL Server Browser service# Note2: Powershell Restart-Service cmdlet handles all dependencies but runs on Windows only# powershell -command "Restart-Service MSSQLSERVER -Force"# After MSSQLSERVER starts - cmdlet starts all previously stopped dependencies.# On Linux SQL Server comes with systemd unit files and can be controlled through systemctl# sudo systemctl restart mssql-server# sudo systemctl status mssql-server# Note3: Make sure you are running commands as admin# Make sure to restart all SQL Server named instances if required# When running on a cluster, the SQL Server Database Engine service# is best managed by using Cluster Administratorbegin {}process {# Stop if error. If not set, result value will be True and assumed there# was no problems. $ErrorActionPreference ="Stop"# Executes once for each pipeline object $JSON = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($_)) $Params=($JSON |ConvertFrom-Json)Write-Output"providerRecordUid=$($Params.providerRecordUid)"Write-Output"resourceRecordUid=$($Params.resourceRecordUid)"Write-Output"userRecordUid=$($Params.userRecordUid)"Write-Output"newPassword=$($Params.newPassword)"Write-Output"oldPassword=$($Params.oldPassword)"Write-Output"user=$($Params.user)"# SQL Server Instances that will be restarted (in list order) [string[]]$Instances ='MSSQLSERVER','MSSQL$instance1','MSSQL$instance2'# restart service(s) on local machineforeach ($Instance in $Instances) { $service =Get-Service $Instanceif ($service.Status -eq'Running') {Restart-Service $Instance -Force } else {Write-Warning"Restart skipped - service $Instance not running, status: $($service.Status)" } }}end {}