SQL Server restart times using Powershell

Question: I have 600 SQL Server Instances and require a daily email to report on the SQL Server restart time and date. The report should be in HTML and as an email attachment.

Answer: Powershell is an excellent method of iterating through a large number of servers , reporting on information gathered.

The Powershell Scripts for DBA can either be managed from SQL Server Agent or a Windows Scheduler. I usually have a dedicated server which manages the scheduler and reporting.

This sample script will iterate through a list of SQL Servers, generate a report and email to recipients.

1) Make sure you create a list of SQL Server instances and place them in a text file.e.g

SERVER1\Instance1
SERVER2\Instance2

Change the location of the sql server list to the location

2)You'll need to add some valid values to the variables.

emailTo

emailFrom

smtpServer

This sql code will execute on every SQL Server Instance listed in the text file

SELECT @@servername as [SQL Server Instance ],sqlserver_start_time as [Restart Time] FROM sys.dm_os_sys_info

Although you could just as easily use other code such as :

SELECT create_date FROM sys.databases WHERE name = 'tempdb' 

--------------------------------SCRIPT START-----------------------------------

#variables 
  $isodate=Get-Date -format s
  $isodate=$isodate -replace(":","")
  $basepath=(Get-Location -PSProvider FileSystem).ProviderPath
  $outputfile="\sql_server_restart_report_" + $isodate + ".html"
  $outputfilefull = $basepath + $outputfile
  $emailFrom = "[email protected]"
  $emailTo = "[email protected]"
  $subject = "SQLServer-DBA.com: Powershell script reporting on SQL Server Restart"
  $body = "SQLServer-DBA.com : Powershell script reporting on SQL Server Restart "
  $smtpServer = "my_smtp_server"
  $filePath = ""

#style for HTML
 $style="<style>TABLE{border-width: 2px;border-style: solid}"
 $style=$style + "TD{border-width: 2px;border-style: solid}"
 $style=$style + "</style>"
 $dt = new-object "System.Data.DataTable"

#email function
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
{
#initate message
$email = New-Object System.Net.Mail.MailMessage
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body
# initiate email attachment
$emailAttach = New-Object System.Net.Mail.Attachment $filePath
$email.Attachments.Add($emailAttach)
#initiate sending email
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($email)
}

#iterate through every sql server instance

 foreach ($svr in get-content "C:\powershell_script\instances_prod.txt"){
    $svr
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "SELECT @@servername as [SQL Server Instance] , sqlserver_start_time as [Start Time] FROM sys.dm_os_sys_info;"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
 }

$dt| select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $style -body "SQL Server Restart Status Report" | Set-Content $outputfilefull

#call email Function
$filePath=$outputfilefull

sendEmail $emailFrom $emailTo $subject $body $smtpServer $filePath

--------------------------------------------------SCRIPT FINISH-------------------------------

Read More

SQL Server – Send email using Powershell

Powershell to HTML

Send email from Powershell with attachment