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 ‘/’.
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="[email protected],[email protected]" $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 [email protected] $emlist "Backup Report - $Date" $MailServer $OutputFile
Output
Use Multiple criteria’s to update the document
Prashanth Jayaram 