SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerShell – SQL Databases Backup Status Report of Multiple Servers

 

This post is a requirement from one of my blog reader.

There are plenty of scripts available to get the backup status of the databases across multiple server. The  requirement is to read a server name and application names from an input file. The server name is used for building a connection and to get the required backup details but where as an app name is used for making an heading in a HTML body.

The Input CSV file consists of Servername, ApplicationName. In this case the App names are separated ‘/’.

InputFile

You need to change the below colored input values as per your environment setup

  • ServerList – Input file where DB Server and Application Names are Listed
  • OutputFile – Used for HTML Email body
  • emlist – You can send it more than one intended receipients. The receipients list are separated by comma
  • MailServer – Valid SMTP servername

CODE:

#Change value of following variables as needed
$ServerList = "f:\Powersql\ServerList.csv"
$OutputFile = "f:\Powersql\Output.htm"
$emlist="pjayaram@appvion.com,prashanth@abc.com"
$MailServer="maa.stmp.com"
 
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
  <TR>
   <TH><B>Database Name</B></TH>
   <TH><B>RecoveryModel</B></TD>
   <TH><B>Last Full Backup Date</B></TH>
   <TH><B>Last Differential Backup Date</B></TH>
   <TH><B>Last Log Backup Date</B></TH>
   </TR>"
  
  
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
Import-Csv $ServerList |ForEach-Object {
$ServerName=$_.ServerName
$AppName=$_.ApplicationName
$HTML += "<TR bgColor='#ccff66'><TD colspan=8 align=center>$ServerName - $AppName</TD></TR>"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
 Foreach($Database in $SQLServer.Databases)
{
$DaysSince = ((Get-Date) - $Database.LastBackupDate).Days
$DaysSinceDiff = ((Get-Date) - $Database.LastDifferentialBackupDate).Days
$DaysSinceLog = ((Get-Date) - $Database.LastLogBackupDate).Days
IF(($Database.Name) -ne 'tempdb' -and ($Database.Name) -ne 'model')
{
if ($Database.RecoveryModel -like "simple" )
{
if ($DaysSince -gt 1){
  $HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>NA</TD>
     </TR>"
}
}
  if ($Database.RecoveryModel -like "full" )
{
if ($DaysSince -gt 1){
  $HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>$($Database.LastLogBackupDate)</TD>
     </TR>"
}
}
if ($DaysSince -lt 1)
{
$HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='GREEN'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>$($Database.LastLogBackupDate)</TD>
     </TR>"
}
 }
}
}
 
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
 
Function sendEmail  
 
{ 
param($from,$to,$subject,$smtphost,$htmlFileName)  
 
$body = Get-Content $htmlFileName 
$body = New-Object System.Net.Mail.MailMessage $from, "$to", $subject, $body 
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
 
}  
$date = ( get-date ).ToString('yyyy/MM/dd')
$emlist
sendEmail pjayaram@appletonideas.com $emlist "Backup Report - $Date" $MailServer $OutputFile

 

Output

BackupStatusReport


PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...