首页 > 编程入门 文章详情

SQL Server备份状态使用PowerShell报告(SQL Server backup status Report with PowerShell)

编程入门 行业动态 更新时间:2024-10-26 18:29:44
SQL Server备份状态使用PowerShell报告(SQL Server backup status Report with PowerShell)

我有一个用于报告多个服务器上的SQL备份状态的PowerShell脚本，它工作正常，但它没有发送邮件的功能。 我添加了那部分，现在我可以通过附件获得邮件了。

唯一值得关注的是，我希望报告显示“不适用”，而不是使用简单恢复模式的缺省日期，或者备份尚未发生。 有人可以建议吗？

这里是代码，以防万一有人需要它，而不像我的要求： 

$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

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

                
最满意答案

                
                    
                        
 更换 
 
<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执行相同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:\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
 

 另见 。 
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  = 'sender@example.com'
$emailTo    = 'recipient@example.com'
$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
 

See also.

更多推荐

本文发布于:2023-08-06 07:29:00，感谢您对本站的认可！
本文链接:https://www.elefans.com/category/jswz/34/1445275.html
版权声明:本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。
本文标签：备份   状态   报告   SQL   Server

发布评论

评论列表 （有 0 条评论）

最近发表

草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍！

热门文章

标签列表