PowerShell Archives » SQLGeeksPro

Tag: PowerShell

PowerShell Script – How many Times SQL Service Restarted

by Dev on

Hi, being SQL DBA there may be situations when you need to find out How many times SQL Service restarted. E.g. Let’s say there is an issue going on with a SQL Server and services got restarted. Your boss has asked you “Is it the first time it has happened? Can you tell me quickly the SQL Service restart history.

This is just one example, whatever may be the scenario, it is always good to have a PowerShell script handy.

The script counts the no. of SQL Server error logs and scan them for the text “SQL Server is starting”.

The output will have the complete message in the error log containing the text along with date and time of occurrence in server local time zone.

Script To Find How Many Times SQL Service Restarted:

####################################################################
# Objective: To Check how many times SQL Services got restarted 
# Author: Tridib Dev
# Source:https://sqlgeekspro.com/
# Parameter: You need to provide SQL Instance Name
#################################################################### 

# Define Input Parameter for SQL Instance Name
param([parameter(Mandatory = $true)] $SQLInstanceName)

Write-host 'SQL Service restart Details for: ' $SQLInstanceName
Write-host '-----------------------------------------------------------'
Write-host " "

# Using SMO for Server Object creation
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstanceName
# Loading sqlserver module
Import-Module sqlserver -DisableNameCheck

# Defining the connection string which helps in establishing connection from remote server
$connection = "Data Source=$SQLInstanceName; Initial Catalog=master; Integrated Security=True; trustservercertificate=True;"

# Get error log list
$logs = Invoke-Sqlcmd -ConnectionString $connection -Query "EXEC xp_enumerrorlogs"

# Count no. of Error logs
$logCount = $logs.Count

Write-Host "Number of SQL Server error logs: $logCount"

foreach ($i in 0..$logCount) {
     
    # Call the ReadErrorLog method
    $logEntries = $srv.ReadErrorLog($i)
    
    # Filter and display results
    $logEntries | 
        Where-Object { ($_.Text -match 'SQL Server is starting') } |
        Format-Table LogDate, ProcessInfo, Text -AutoSize
}

Once you execute the script in PowerShell ISE, it will ask for the SQL Instance name for which you want to find the SQL Service restart history:

PowerShell Script How Many Times SQL Service Restarted

Example Result Set:

The output shows there are 100 SQL Error log files and the script has scanned them all. The SQL Service has restarted 3 times.

Hope it is is helpful. Please feel free to customize the script as per your requirements. Also you can use the same script to scan any other messages/errors by replacing the text.

Please leave a comment about your experience. You may also like to check the following interesting scripts.

Powershell Script to Validate SQL Servers

Powershell Script to Create Folders

List all permissions for a user in all or selective databases

Powershell Script to Create Folders

Tagged as: PowerShell, SQL Service Restart

{ Add a Comment }

Powershell Script to Create Folders

by Dev on

This article will help you with Powershell Script to Create Folders. Being DBA you may get into many situations when you need to create many folders.

Let me give a scenario. You have a SQL Instance of around 100 databases and need to create directory with the database names.

Powershell Script to Create Folders

Go to Start > Search and type “Powershell”. You can use any of the highlighted option.

Powershell Script to Create Folders

Here in this example I’ll be showing the example from first option. i.e. PowerShell ISE. Right Click > Run As Administrator

Powershell Script to Create Folders

The one liner code is as follows. Here I am creating a new folder named “folder” in the path ‘C:\BackupData\’
New-item -Path 'C:\BackupData\folder' -ItemType Directory

The following screen shot shows how it looks in the console.

Powershell Script to Create Folders

Now lets see how you can use this to create multiple folders.
First create a folder list in a notepad and save it in a notepad. In this example the file is “FolderNames.txt”
Powershell Script to Create Folders

$foldername= Get-Content -Path 'C:\Temp\Test\FolderNames.txt' #Provide the Path where the list is kept
$dirpath='C:\Temp\Test' #The Path where you need to create the folders
foreach ($folder in $foldername)
{
New-Item -ItemType Directory -Path $dirpath\$folder
}

Copy the code in the PowerShell ISE and click on the Play button to execute it. The folder names will be displayed in the bottom section as you can see in the below screen shot.
Powershell Script to Create Folders

Validate the path:
Powershell Script to Create Folders

Tagged as: PowerShell

{ Add a Comment }

Powershell Script to Validate SQL Servers

by Dev on

This article is about a Powershell script to Check SQL Server Database Status and Services remotely. This will help in validating SQL Server after Windows patching.

To put in another way, DBAs are responsible for validating SQL Servers after server maintenance. Usually DBAs need to do a sanity check of the SQL Server after planned or unplanned maintenance.

The most common checks while doing a SQL Server validation:

Validate SQL Services are up and running
SQL Databases are online
There is no error in the error log
Authentication protocol after the restart (Kerberos Or NTLM)

Now imagine if you have a large number of servers, how much time you need to spend on such activity. The below simple yet effective powershell script will save a lot of time.

Powershell script to Check SQL Server Database Status and Services:

###########################################################################################################
#Objective: To Check SQL Service status, Database status, Errors in Errorlog and Authentication protocol  #
#Author: Tridib Dev                                                                                       #
#Source:https://sqlgeekspro.com/                                                                          #
#Parameter: SQL Instance Name                                                                             #
###########################################################################################################
param ([parameter(Mandatory = $true)] $SQLServer)
        
Write-host 'SQL Service Status for: ' $SQLServer
Write-host '---------------------------------------------'
Write-host " "
Get-WmiObject -Class Win32_Service -ComputerName $SQLServer | Where-Object {$_.Name -like "*SQL*"} | Format-List -Property Displayname, state, startName
Import-Module SQLPS -DisableNameChecking
$q1 = "select name as DatabaseName, state_desc as Status from sys.databases where state_desc <> 'online'"
$q2 = @"
CREATE TABLE #ErrorLog(
   LogDate DATETIME,
   ErrorSource NVARCHAR(MAX),
   ErrorMessage NVARCHAR(MAX)
)

INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
       EXEC xp_readerrorlog 0,1, "Error"
select LogDate, ErrorMessage from #ErrorLog 
drop table #ErrorLog
"@
$q3 = "select auth_scheme from sys.dm_exec_connections where session_id=@@spid"

$r1 = Invoke-Sqlcmd -ServerInstance $SQLServer $q1
$r2 = Invoke-Sqlcmd -ServerInstance $SQLServer $q2
$r3 = Invoke-Sqlcmd -ServerInstance $SQLServer $q3

write-host 'Database status'
Write-host '-----------------'
Write-host " "
Write-host $r1.itemArray
Write-host '-----------------'
write-host 'ErrorLog:-'
Write-host '-----------------'
Write-host " "
Write-host $r2.itemArray
Write-host '-----------------'
Write-host " "
Write-host 'Authentication Type'
Write-host '-----------------'
Write-Host $r3.itemArray

Sample Output:

Powershell to Check SQL Database Status — SQL Service Status

Note: You can run the script in PowerShell ISE or download it from ValidateSQLServerThe output will be in console. I have written this script keeping it simple and have not used advanced modules so that you don’t have to install any modules. You just need SQLPS module.
Hope this will help in saving lot of your time in validating SQL Servers after any maintenance. You may also like Powershell Script to find SQL Instance Name for a Remote Server

Tagged as: PowerShell, Validate SQL Server

{ Add a Comment }

PowerShell Script to find SQL Instances on remote server

by Dev on

This article is about a PowerShell script to find sql instances on remote server. Not to mention the remote server can be standalone or Cluster.
When I got the requirement to develop the script, I thought it is not a big deal. Generally speaking, PowerShell will have some direct commands to do so and my script is few searches away.
But when I put my feet in, I got to know it was not as easy as I thought.
Especially the real challenge comes in when you have to list the SQL Instances for a SQL Cluster. The script should be intelligent enough to find out if the server is cluster or standalone and accordingly list the SQL Instances.

Note:The script should run from a server where FailOver Clustering Feature is installed.

PowerShell script to find sql instance name on remote server

Param(
    [Parameter(Mandatory=$true)]
     $ComputerName
)

if ((Get-WMIObject -Class MSCluster_ResourceGroup -ComputerName $ComputerName -Namespace root\mscluster -ErrorAction SilentlyContinue) -ne $null)
 {  
    Import-Module FailoverClusters
    get-clusterresource -Cluster $ComputerName  -ErrorAction SilentlyContinue|
    where-object {$_.ResourceType -like “SQL Server”} | 
    get-clusterparameter VirtualServerName,InstanceName | group-object ClusterObject | 
    select-object @{Name = “SQLInstance”;Expression = {[string]::join(“\”,($_.Group | select-object -expandproperty Value))}} 
    } 
else {
$SQLInstances = Invoke-Command -ComputerName $ComputerName {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}
    foreach ($sql in $SQLInstances) {
       [PSCustomObject]@{
           ServerName = $sql.PSComputerName
           InstanceName = $sql
       }
   }  
   }

Not to mention, you can execute it from PowerShell ISE or from PowerShell Command prompt. Let’s see both examples.

Execute The Script Through Windows PowerShell ISE:

PowerShell Script to find SQL Instances on remote server Once you execute the script you’ll be prompted to provide the Server Name as you can see in the last line in above screen shot.
Sample OutPut:

Execute the Script from Windows PowerShell Command Prompt:

Only difference is to provide the server name while running the script as shown in the screen shot. Also the SQL Instance “PC01” is a cluster. Hence you don’t have to bother if the server is standalone or cluster. Hope this helps.
Let me know if you have any questions or comments.

Tagged as: PowerShell, SQL Instance

{ 2 Comments }