Invoke-InstanceCommand

DBSystems: Starts or stops a SQL Server Instance

Script

#Requires -Version 5.0
#Requires -Modules SQLServer

[CmdLetBinding()]
Param(
    [Parameter(Mandatory = $true)]   
    [string]$ServerInstance,    
    [Parameter(Mandatory = $true)]   
    [pscredential]$ServerCredential,  
    [ValidateSet("Start","Stop")]   
    [string]$Command ="Start",          
    [int]$ConnectionTimeout = 30
)

function Get-SqlServerInstanceInternal {
    [CmdLetBinding()]
    Param(
        [Parameter(Mandatory = $true)]   
        [string]$ServerInstance,    
        [pscredential]$ServerCredential,
        [int]$ConnectionTimeout = 30
    )
    try {
        [hashtable]$cmdArgs = @{
            'ErrorAction' = 'Stop'
            'Confirm' = $false
            'ServerInstance' = $ServerInstance
            'ConnectionTimeout' = $ConnectionTimeout
        }
        if ($null -ne $ServerCredential) {
            $cmdArgs.Add('Credential', $ServerCredential)
        }
        return Get-SqlInstance @cmdArgs
    } catch {
        throw
    }
}

Import-Module SQLServer

try {
    $Properties = @('DisplayNameOrName','Status','Edition','InstanceName','DomainInstanceName','LoginMode','ServerType','ServiceStartMode','ComputerNamePhysicalNetBIOS')
    $instance = Get-SqlServerInstanceInternal -ServerInstance $ServerInstance -ServerCredential $ServerCredential -ConnectionTimeout $ConnectionTimeout
    
    [hashtable]$cmdArgs = @{
        'ErrorAction' = 'Stop'
        'InputObject' = $instance
        'Credential' = $ServerCredential
        'Confirm' = $false
    }                            
    
    if ($Command -eq "Stop") {
        Stop-SqlInstance @cmdArgs
    } else {
        Start-SqlInstance @cmdArgs
    }  

    # Refresh and show status
    $getArgs = @{
        'ErrorAction' = 'Stop'
        'Confirm' = $false
        'Credential' = $ServerCredential
        'ServerInstance' = $ServerInstance
        'ConnectionTimeout' = $ConnectionTimeout
    }

    $result = Get-SqlInstance @getArgs | Select-Object $Properties
    Write-Output $result
} catch {
    throw
}

Customize

ServerInstance string

Specifies the name of the target computer including the instance name, e.g. MyServer\Instance

ServerCredential pscredential

Specifies a PSCredential object for the connection to the SQL Server. ServerCredential is ONLY used for SQL Logins. When you are using Windows Authentication you don't specify -Credential. It is picked up from your current login.

Command string

The action to perform: Start or Stop the SQL Instance

ConnectionTimeout int

Specifies the time period to retry the command on the target server