SQL Server backup status Report with PowerShell

I got a PowerShell script for reporting SQL backup status on multiple servers, it works fine but it had no function to send mail. I added that part and now I am able to get the mail with the attachment.

The only concern is, I want the report to show "NA" and not a default date where the satabase is in Simple Recovery Model or if backup has not happened. Can someone please advise?

Here is the code, just in case someone needs it unlike my requirement:

$ServerList = Get-Content "Serverlist location"
$OutputFile = "to save the report location"

$titleDate = Get-Date -UFormat "%m-%d-%Y - %A"
$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
foreach ($ServerName in $ServerList)
{
    $HTML += "<TR bgColor='#ccff66'><TD colspan=5 align=center><B>$ServerName</B></TD></TR>"

    $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
    foreach ($Database in $SQLServer.Databases)
    {
        $HTML += "<TR>
                    <TD>$($Database.Name)</TD>
                    <TD>$($Database.RecoveryModel)</TD>
                    <TD>$($Database.LastBackupDate)</TD>
                    <TD>$($Database.LastDifferentialBackupDate)</TD>
                    <TD>$($Database.LastLogBackupDate)</TD>
                </TR>"
    }
}

$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile

$emailFrom = "send email address"
$emailTo = "recipient email address"
$subject = "Xyz Report"
$body = "your words "
$smtpServer = "Smptp server"
$filePath = "location of the file you want to attach"

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

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

Solution

Replace

<TD>$($Database.LastBackupDate)</TD>

with something like

<TD>$(if ($Database.RecoveryModel -eq 'Simple' -or $Database.LastBackupDate -eq '01/01/0001 00:00:00') {'NA'} else {$Database.LastBackupDate})</TD>

Do the same for LastDifferentialBackupDate and LastLogBackupDate.

With that said, I strongly recommend looking into calculated properties, ConvertTo-Html, and Send-MailMessage, which would allow you to greatly simplify your code:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

$emailFrom  = '[email protected]'
$emailTo    = '[email protected]'
$subject    = 'Xyz Report'
$smtpServer = 'mail.example.com'

$style = @'
<style type="text/css">
...
</style>
'@

$msg = Get-Content 'C:\path\to\serverlist.txt' |
       ForEach-Object {New-Object 'Microsoft.SqlServer.Management.Smo.Server' $_} |
       Select-Object -Expand Databases |
       Select-Object Name, RecoveryModel,
           @{n='LastBackupDate';e={if ($_.RecoveryModel -eq 'Simple' -or $_.LastBackupDate -eq '01/01/0001 00:00:00') {'NA'} else {$_.LastBackupDate}}},
           @{n='LastDifferentialBackupDate';e={if ($_.RecoveryModel -eq 'Simple' -or $_.LastDifferentialBackupDate -eq '01/01/0001 00:00:00') {'NA'} else {$_.LastDifferentialBackupDate}}},
           @{n='LastLogBackupDate';e={if ($_.RecoveryModel -eq 'Simple' -or $_.LastLogBackupDate -eq '01/01/0001 00:00:00') {'NA'} else {$_.LastLogBackupDate}}} |
       ConvertTo-Html -Head $style | Out-String

Send-MailMessage -From $emailFrom -To $emailTo -Subject $subject -Body $msg -BodyAsHtml -SmtpServer $smtpServer