SQL Server restart times using Powershell

09 December,2016 by Jack Vamvas

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 = "jvamvas@sqlsever-dba.com"
  $emailTo = "jvamvas@sqlserver-dba.com"
  $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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server restart times using Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer