SQL SERVER – PowerShell Script – When Was SQL Server Last Restarted?

I have always wanted to learn a bit of scripting and I was always searching for a good reason to learn something new. As I said, this is my learning and I am open to learning some scripting tricks from you too. So do let me know how the below script can be optimized in your comments. Now, what was my reason to learn this script?

Someone asked me, “How can I find out when SQL Server was started?” There are multiple ways to find out, but I took the simple route and said – “Why don’t you check the SQL Server Error logs?” I know, the SQL Server error logs can get recycled and this data maynot be available. But in most cases, this gives us some indication.

So the other question was how can we script this requirement? So the below script is written to read SQL Server ErrorLog, find a specific text and report the number of times the string was found. We also show the time when the last/recent occurrence was found.

param
(
 $serverinstance = ".", #Change this to your instance name
 $stringToSearchFor = "Starting up database ''master''"
)
# Load Assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
# Server object
$server = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $serverinstance
$searchCriteria = "Text like '%{0}%'" -f $stringToSearchFor
$SQLErrorLogs = $Server.EnumErrorLogs()
$SearchEntry = @()
ForEach ($SQLErrorLog in $SQLErrorLogs)
{
 $SearchResult = $server.ReadErrorLog($SQLErrorLog.ArchiveNo).select($searchCriteria) | Select-Object -Property LogDate, Text
 $SearchEntry = $SearchEntry + $searchResult
} 
$MeasureOccurences = $SearchEntry | Measure-Object -Property LogDate -Minimum -Maximum
$SQLSearchInfo = New-Object psobject -Property @{
 SearchText = $stringToSearchFor
 Occurances = $MeasureOccurences.Count
 MinDateOccurred = $MeasureOccurences.Minimum
 MaxDateOccurred = $MeasureOccurences.Maximum
 }
Write-Output $SQLSearchInfo | FT -AutoSize 

Do let me know if you ever used such scripting capability to search your errorlogs? You can change the search string and the default instance to your environment.
I wrote this generically because I was planning to use this to find error messages inside ErrorLogs like “I/O requests taking longer than 15 seconds to complete”. The script can be modified to your needs. Do share your scripts too over the comments so that others can benefit from the sharing.

Reference: Pinal Dave (https://blog.sqlauthority.com)