Sql Services Information with dbatools

Today I found sql services information with dbatools script where we need to execute a function to get it created , once function is created we can get needed information by calling that function and passing some parameters like computer names, Type of sql service, e.t.c.,

To run this you need to open PowerShell with Run As Administrator, also the Login account from which we are executing needs to have local admin Privileges on target \ destination server. Output of this function can be displayed in a Table Format or in a Grid View . This function I got from PowerShellgallery site. The following function displays the current status of all SQL-related services on the specified Computer or Instance Name. Additionally, we can get solely SQL-related Running Services and SQL-related Stopped Services.

The output from the below function is user-friendly, and you can use the Formate-Table parameter to list the SQL Services Status view and you can also export the list to .csv as well.

You can get sql Services information for Single or Multiple Servers , go through the Synopsis.

Function Get-DbaSqlService
{
<#
    .SYNOPSIS
    Gets the SQL Server related services on a computer.
 
    .DESCRIPTION
    Gets the SQL Server related services on one or more computers.
 
    Requires Local Admin rights on destination computer(s).
 
    .PARAMETER ComputerName
    The SQL Server (or server in general) that you're connecting to. This command handles named instances.
 
    .PARAMETER Credential
    Credential object used to connect to the computer as a different user.
 
    .PARAMETER Type
    Use -Type to collect only services of the desired SqlServiceType.
    Can be one of the following: "Agent","Browser","Engine","FullText","SSAS","SSIS","SSRS"
 
    .PARAMETER Silent
    Use this switch to disable any kind of verbose messages
 
    .NOTES
    Author: Klaas Vandenberghe ( @PowerDBAKlaas )
 
    dbatools PowerShell module (https://dbatools.io)
    Copyright (C) 2016 Chrissy LeMaire
    This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
    You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/.
 
    .LINK
    https://dbatools.io/Get-DbaSqlService
 
    .EXAMPLE
    Get-DbaSqlService -ComputerName sqlserver2014a
 
    Gets the SQL Server related services on computer sqlserver2014a.
 
    .EXAMPLE
    'sql1','sql2','sql3' | Get-DbaSqlService
 
    Gets the SQL Server related services on computers sql1, sql2 and sql3.
 
    .EXAMPLE
    Get-DbaSqlService -ComputerName sql1,sql2 | Out-Gridview
 
    Gets the SQL Server related services on computers sql1 and sql2, and shows them in a grid view.
 
    .EXAMPLE
    Get-DbaSqlService -ComputerName $MyServers -Type SSRS
 
    Gets the SQL Server related services of type "SSRS" (Reporting Services) on computers in the variable MyServers.
 
#>
[CmdletBinding()]
Param (
  [parameter(ValueFromPipeline = $true)]
  [Alias("cn","host","Server")]
  [string[]]$ComputerName = $env:COMPUTERNAME,
  [PSCredential] $Credential,
  [ValidateSet("Agent","Browser","Engine","FullText","SSAS","SSIS","SSRS")][string]$Type,
  [switch]$Silent
)

BEGIN
  {
  $ComputerName = $ComputerName | ForEach-Object {$_.split("\")[0]} | Select-Object -Unique
  $TypeClause = switch($Type){ "Agent" {" = 2"} "Browser" {" = 7"} "Engine" {" = 1"} "FulText" {"= 3 OR SQLServiceType = 9"} "SSAS" {" = 5"} "SSIS" {" = 4"} "SSRS" {" = 6"} default {"> 0"} }
  }
PROCESS
  {
        foreach ( $Computer in $ComputerName )
        {
            $Server = Resolve-DbaNetworkName -ComputerName $Computer -Credential $credential
            if ( $Server.FullComputerName )
            {
                $Computer = $server.FullComputerName
                Write-Message -Level Verbose -Message "Getting SQL Server namespace on $Computer via CIM (WSMan)"
                $namespace = Get-CimInstance -ComputerName $Computer -NameSpace root\Microsoft\SQLServer -ClassName "__NAMESPACE" -Filter "Name Like 'ComputerManagement%'" -ErrorAction SilentlyContinue |
                            Where-Object {(Get-CimInstance -ComputerName $Computer -Namespace $("root\Microsoft\SQLServer\" + $_.Name) -Query "SELECT * FROM SqlService" -ErrorAction SilentlyContinue).count -gt 0} |
                            Sort-Object Name -Descending | Select-Object -First 1
                if ( $namespace.Name )
                {
                    Write-Message -Level Verbose -Message "Getting Cim class SqlService in Namespace $($namespace.Name) on $Computer via CIM (WSMan)"
                    try
                    {
                        Get-CimInstance -ComputerName $Computer -Namespace $("root\Microsoft\SQLServer\" + $namespace.Name) -Query "SELECT * FROM SqlService WHERE SQLServiceType $TypeClause" -ErrorAction SilentlyContinue |
                        ForEach-Object {
                            [PSCustomObject]@{
                                ComputerName = $_.HostName
                                ServiceName = $_.ServiceName
                                DisplayName = $_.DisplayName
                                StartName = $_.StartName
                                ServiceType = switch($_.SQLServiceType){1 {'Database Engine'} 2 {'SQL Agent'} 3 {'Full Text Search'} 4 {'SSIS'} 5 {'SSAS'} 6 {'SSRS'} 7 {'SQL Browser'} 8 {'Unknown'} 9 {'FullTextFilter Daemon Launcher'}}
                                State = switch($_.State){ 1 {'Stopped'} 2 {'Start Pending'}  3 {'Stop Pending' } 4 {'Running'}}
                                StartMode = switch($_.StartMode){ 1 {'Unknown'} 2 {'Automatic'}  3 {'Manual' } 4 {'Disabled'}}
                                }
                            }
                     }
                     catch
                     {
                        Write-Message -Level Warning -Message "No Sql Services found on $Computer via CIM (WSMan)"
                     }
                }
                else
                {
                  Write-Message -Level Verbose -Message "Getting computer information from $Computer via CIMsession (DCOM)"
                  $sessionoption = New-CimSessionOption -Protocol DCOM
                  $CIMsession = New-CimSession -ComputerName $Computer -SessionOption $sessionoption -ErrorAction SilentlyContinue -Credential $Credential
                  if ( $CIMSession )
                  {
                    Write-Message -Level Verbose -Message "Get ComputerManagement Namespace in CIMsession on $Computer with protocol DCom."
                    $namespace = Get-CimInstance -CimSession $CIMsession -NameSpace root\Microsoft\SQLServer -ClassName "__NAMESPACE" -Filter "Name Like 'ComputerManagement%'" -ErrorAction SilentlyContinue |
                    Where-Object {(Get-CimInstance -CimSession $CIMsession -Namespace $("root\Microsoft\SQLServer\" + $_.Name) -Query "SELECT * FROM SqlService" -ErrorAction SilentlyContinue).count -gt 0} |
                    Sort-Object Name -Descending | Select-Object -First 1
                  }
                  else
                  {
                    Write-Message -Level Warning -Message "can't create CIMsession via DCom on $Computer"
                    continue
                  }
                  if ( $namespace.Name )
                  {
                      Write-Message -Level Verbose -Message "Getting Cim class SqlService in Namespace $($namespace.Name) on $Computer via CIM (DCOM)"
                      try
                      {
                          Get-CimInstance -CimSession $CIMsession -Namespace $("root\Microsoft\SQLServer\" + $namespace.Name) -Query "SELECT * FROM SqlService WHERE SQLServiceType $TypeClause" -ErrorAction SilentlyContinue |
                          ForEach-Object {
                              [PSCustomObject]@{
                                  ComputerName = $_.HostName
                                  ServiceName = $_.ServiceName
                                  DisplayName = $_.DisplayName
                                  StartName = $_.StartName
                                  ServiceType = switch($_.SQLServiceType){1 {'Database Engine'} 2 {'SQL Agent'} 3 {'Full Text Search'} 4 {'SSIS'} 5 {'SSAS'} 6 {'SSRS'} 7 {'SQL Browser'} 8 {'Unknown'} 9 {'FullTextFilter Daemon Launcher'}}
                                  State = switch($_.State){ 1 {'Stopped'} 2 {'Start Pending'}  3 {'Stop Pending' } 4 {'Running'}}
                                  StartMode = switch($_.StartMode){ 1 {'Unknown'} 2 {'Automatic'}  3 {'Manual' } 4 {'Disabled'}}
                                  }
                           }
                        }
                        catch
                        {
                          Write-Message -Level Warning -Message "No Sql Services found on $Computer via CIM (DCOM)"
                        }
                    if ( $CIMsession ) { Remove-CimSession $CIMsession }
                  }
                  else
                  {
                  Write-Message -Level Warning -Message "No ComputerManagement Namespace on $Computer. Please note that this function is available from SQL 2005 up."
                  }
                }
            }
            else
            {
                Write-Message -Level Warning -Message "Failed to connect to $Computer"
            }
        }
    }
}

Output of above PowerShell Function looks like below with Format-Table :

Output of above PowerShell Function looks like below with Out-Gridview :

Sql Services State Report with Colour Coding in Html file

I tried to look out in google regarding Sql Services State Report with Colour Coding where if the instance State is Running then it should indicate GREEN and if the instance state is Stopped then it should indicate with RED .

Today I have got the script by looking it into multiple sites and combined those scripts into one and finally got the output which is needed and it helps me to identify easily which instance is Running or Stopped.

Thanks for the original script writers for the script lines in PowerShell which makes DBA’s life easier , Here using CSS code for colour coding ,CIM Cmdlets to get the all Sql Services State then converting to HTML code in a table and it is stored in a Variable. The reports will be gathered and combined into a Single HTML Report ,finally the output of the report is generated to an HTML file which will be stored in physical Location from the Directory where we are executing.

Find the Script below :

#CSS code
$header = @"
<style>

    h1 {

        font-family: Arial, Helvetica, sans-serif;
        color: #e68a00;
        font-size: 28px;

    }

    
    h2 {

        font-family: Arial, Helvetica, sans-serif;
        color: #000099;
        font-size: 16px;

    }

    
    
   table {
		font-size: 12px;
		border: 0px; 
		font-family: Arial, Helvetica, sans-serif;
	} 
	
    td {
		padding: 4px;
		margin: 0px;
		border: 0;
	}
	
    th {
        background: #395870;
        background: linear-gradient(#49708f, #293f50);
        color: #fff;
        font-size: 11px;
        text-transform: uppercase;
        padding: 10px 15px;
        vertical-align: middle;
	}

    tbody tr:nth-child(even) {
        background: #f0f0f2;
    }
    


    #CreationDate {

        font-family: Arial, Helvetica, sans-serif;
        color: #ff3300;
        font-size: 12px;

    }



    .StopStatus {

        color: #ff0000;
    }
    
  
    .RunningStatus {

        color: #008000;
    }




</style>
"@

#The command below will get the name of the computer
$ComputerName = "<h1>Computer name: $env:computername</h1>"

#The command below will get SQL services information, convert the result to HTML code as table and store it to a variable
$ServicesInfo = Get-CimInstance -ComputerName DESKTOP-02JIB76 -ClassName Win32_Service -Filter "Name like '%sql%'"  |ConvertTo-Html -Property Name,DisplayName,State -Fragment -PreContent "<h2>Services Information</h2>"
$ServicesInfo = $ServicesInfo -replace '<td>Running</td>','<td class="RunningStatus">Running</td>'
$ServicesInfo = $ServicesInfo -replace '<td>Stopped</td>','<td class="StopStatus">Stopped</td>'

  
#The command below will combine all the information gathered into a single HTML report
$Report = ConvertTo-HTML -Body "$ComputerName $ServicesInfo" -Head $header -Title "Computer Information Report" -PostContent "<p id='CreationDate'>Creation Date: $(Get-Date)</p>"

#The command below will generate the report to an HTML file
$Report | Out-File .\sql-Services-Status-Report.html

PowerShell Script to get the Report Of Sql Services Status

There is a requirement from client that due to some ongoing issues on 10 servers client asked us to check sql services Status everyday if they are running or stopped as these servers are not yet added to Monitoring .

So, thought do this check using PowerShell that to retrieve the sql services status information. We can use Get- Service command to get required output but this works for individual server as I want to automate to retrieve information for all 10 servers in one go so, here we have used CIM commandlets to get our sql Services status report. Also we need sql Services Start Time for all Servers. The output has the fields Hostname, Name, Caption, State, ProcessID, StartName(ServiceAccount) and StartTime. Below is the PowerShell script to get the sql Services Running or Stopped State where you need to provide ComputerNames or ServerNames :

Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

From above you can see it gives all the instances installed on the Machine with Running or Stopped State, along with sql Services Start Time

You can pass Multiple ComputerNames in Script by Separating with Coma(,) like

Invoke-Command -ComputerName DESKTOP-02JIB76,Node1,Node2 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

If you want to get information of only Running or only Stopped State Services

Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'running'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName
Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'Stopped'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

Backups Information for Multiple Servers using PowerShell

Backup Information Using Powershell :

For getting our Backup Information here using SQLPS Module which was already installed in my Machine ,The PowerShell module for SQL Server is not included by default with the Windows operating system. SQL Server includes PowerShell for SQL Server (sqlps), but you may need to update it to the latest version. PowerShell is not included in SQL Server Management Studio (SSMS). I heard this SQLPS will get installed along with SQL 2014 client tools installed on the server. For our SQL environment, we use a variety of backup tools, including NetApp snap manager, Commvault tape backup, TSM, EMC Avmar backup and even SQL Native backups. There are multiple reasons to use above mentioned backup tools .

Get-BackupInfo is a Microsoft SQL Server function that returns database backup information for one or more databases. Full database backups, transaction log backups, and differential backups are all included. The Microsoft.SqlServer.Management cmdlet is the inspiration for this function. Smo.Backup is a backup class. The properties on the Smo object correspond to the parameters on this class.

To check if SQLPS Module is installed on your machine we can use Powershell to check this by using below command :

Get-Module -ListAvailable

Once you execute above command in PowerShell you will see all the list of modules installed in your machine

Below is the SQLPS module for SQL 2014

If you have not seen SQLPS module installed on your machine then please download it from Microsoft site and Import them using Import-Module command.

After Importing the Module you need to stop the warning message to be generated because some of the modules that uses unapproved verbs which results in throwing unnecessary warning messages so its better to suppress those warning alerts or messages. Use below command to not getting warning message:

Import-Module -Name SQLPS -DisableNameChecking

The below PowerShell Function gives you the Backup Information of Single or Multiple Instances:

#Requires -Version 3.0
function Get-BackupInfo {
<#
.SYNOPSIS
Returns database backup information for a Microsoft SQL Server database.

.DESCRIPTION
Get-BackupInfo is a function that returns database backup information for
one or more Microsoft SQL Server databases.

.PARAMETER ComputerName
The computer that is running Microsoft SQL Server that you’re targeting to
query database backup information for.

.PARAMETER InstanceName
The instance name of SQL Server to return database backup information for.
The default is the default SQL Server instance.

.PARAMETER DatabaseName
The database(s) to return backup information for. The default is all databases.

.EXAMPLE
Get-BackupInfo -ComputerName sql01

.EXAMPLE
Get-BackupInfo -ComputerName sql01 -DatabaseName master, msdb, model

.EXAMPLE
Get-BackupInfo -ComputerName sql01 -InstanceName SQL1 -DatabaseName master,msdb, model

.EXAMPLE
'master', 'msdb', 'model' | Get-BackupInfo -ComputerName sql01

.INPUTS
String

.OUTPUTS
PSCustomObject
#>

   [CmdletBinding()]
   param (
      [Parameter(Mandatory,
      ValueFromPipelineByPropertyName)]
      [Alias('ServerName','PSComputerName')]
      [string[]]$ComputerName,

      [Parameter(ValueFromPipelineByPropertyName)]
      [ValidateNotNullOrEmpty()]
      [string[]]$InstanceName = 'Default',
    
      [Parameter(ValueFromPipelineByPropertyName)]
      [ValidateNotNullOrEmpty()]
      [string[]]$DatabaseName = '*'

   )

   BEGIN {
      $problem = $false
      Write-Verbose -Message "Attempting to load SQL Module if it's not already loaded"
      if (-not (Get-Module -Name SQLPS)) {
          try {
              Import-Module -Name SQLPS -DisableNameChecking -ErrorAction Stop
          }
          catch {
              $problem = $true
              Write-Warning -Message "An error has occurred.&amp;nbsp; Error details: $_.Exception.Message"
          }
      }
   }

   PROCESS {
       foreach ($Computer in $ComputerName) {
            foreach ($Instance in $InstanceName) {
                Write-Verbose -Message 'Checking for default or named SQL instance'
                If (-not ($problem)) {
                    If (($Instance -eq 'Default') -or ($Instance -eq 'MSSQLSERVER')) {
                       $SQLInstance = $Computer
                    }
                    else {
                       $SQLInstance = "$Computer\$Instance"
                    }
                    $SQL = New-Object('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList $SQLInstance
                }

                if (-not $problem) {
                     foreach ($db in $DatabaseName) {
                         Write-Verbose -Message "Verifying a database named: $db exists on SQL Instance $SQLInstance."
                         try {
                             if ($db -match '\*') {
                                  $databases = $SQL.Databases | Where-Object Name -like "$db"
                             }
                             else {
                                  $databases = $SQL.Databases | Where-Object Name -eq "$db"
                             }
                         }
                         catch {
                             $problem = $true
                             Write-Warning -Message "An error has occurred.&amp;nbsp; Error details: $_.Exception.Message"
                         }
                         if (-not $problem) {
                             foreach ($database in $databases) {
                                  Write-Verbose -Message "Retrieving information for database: $database."
                                  [PSCustomObject]@{
                                      ComputerName = $SQL.Information.ComputerNamePhysicalNetBIOS
                                      InstanceName = $Instance
                                      DatabaseName = $database.Name
                                      LastBackupDate = $database.LastBackupDate
                                      LastDifferentialBackupDate = $database.LastDifferentialBackupDate
                                      LastLogBackupDate = $database.LastLogBackupDate
                                      RecoveryModel = $database.RecoveryModel
                             }
                          }
                     }
                 }
             }
         }
      }
   }
}

Execute above function and call the function using parameters like below :

Getting Backup information for my Default Instance and output will be generating lie below

Get-BackupInfo -ComputerName DESKTOP-02JIB76 |Out-GridView 

We can retrieve Backup Information for multiple Named Instances by using below command just provide InstanceName without servername\

Get-BackupInfo -ComputerName DESKTOP-02JIB76 -InstanceName SQL1,SQL2 |Out-GridView

From above screenshot we can say only Full Backup is happening for the databases there is NO Differential and Logbackups running for databases.

If you want the output to get in PowerShell window itself instead of Gridview then run below command :

Get-BackupInfo -ComputerName DESKTOP-02JIB76 -InstanceName SQL1,SQL2 |Format-Table

PowerShell Script to Check SQL Services status,start,stop,restart

There are many ways to check the sql services status . we will start with simple command in PowerShell ,ask PowerShell to tell you all of the commands related to services by using below command:

Get-Command -Noun Service

From the above screen shot look at the commands which we use to check the sql services status, start, stop and restart :

Get-Service ServiceName – this is to check the sql services status like Running\Stopped

Example:

Get-Service ‘MSSQL$*’ – This command gives all the Named Instances Installed on your Windows Box

Get-Service ‘MSSQL*’ – This command gives both Named and default Instances Installed on your Windows Box.

start-Service ServiceName – This is to start your specified service

Example :

start-Service MSSQLSERVER – command for Default instance start

start-Service MSSQL$SQL1 – Command for Named Instance start

stop-Service ServiceName – This is to stop your specified service

Example :

stop-Service MSSQLSERVER – command for Default instance stop

stop-Service MSSQL$SQL1 – Command for Named Instance stop

Restart-Service ServiceName – This is to restart your specified service

Example :

Restart-Service MSSQLSERVER – command for Default instance Restart

Restart-Service MSSQL$SQL1 – Command for Named Instance Restart

All the above commands will not work as we have dependent Agent service for Database Engine. So, we need to use -Force parameter in command to force restart as shown below :

restart-service -force MSSQLSERVER – for default Database engine restart

restart-service -force SQLSERVERAGENT – for default Agent engine restart

restart-service -force ‘MSSQL$SQL1’ -for Named Database engine restart

restart-service -force ‘SQLAgent$SQL1’ – for Named Agent engine restart

We can also use below PowerShell script to check the sql services status :

By using below PowerShell Command you can find all SQL related services on our Windows Machine :

Get-Service *sql* |select-object ServiceName,status|Format-Table -AutoSize

OR

By using below PowerShell Command you can find all SQL related services on Node1 Server

Example :

Get-Service -ComputerName NODE1 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status

OR

By using below PowerShell Command you can find all SQL related services on multiple servers[Separate each server name by comma]

Get-Service -ComputerName NODE1,NODE2 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status

Hope this post helps you a lot, I’ve tested above scripts by executing it on SQL Services(both default and Named instance) from my end and works good . Please do some testing before executing it in production environment.