SQL Server备份状态报表

我得到了一个PowerShell脚本，用于报告多个服务器上的SQL备份状态，它工作得很好，但没有发送邮件的功能。我添加了这部分，现在我可以收到带有附件的邮件了。

唯一的问题是，我希望报告显示"NA"，而不是数据库处于简单恢复模型或未发生备份的默认日期。有人能给点建议吗?

下面是代码，以防有人需要它，而不是我的要求:

$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

Replace

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

和

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

对LastDifferentialBackupDate和LastLogBackupDate做同样的处理。

话虽如此，我强烈建议您研究计算属性ConvertTo-Html和Send-MailMessage，它们将允许您极大地简化代码:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$emailFrom  = 'sender@example.com'
$emailTo    = 'recipient@example.com'
$subject    = 'Xyz Report'
$smtpServer = 'mail.example.com'
$style = @'
<style type="text/css">
...
</style>
'@
$msg = Get-Content 'C:pathtoserverlist.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

看。

相关内容

最新更新

热门标签：