I want to receive a daily email listing SQL backups older than X days. But my report lists ALL the backups. I am able to color-code the rows with old backups but I am not sure how to get the rest of the “good” backups out of the report. Here is a section of the code. I need to all the IF conditions higher up in the report but I don’t know how.
This should give you a list of database, per server, where backups are older than 2 days. I was unable to test the ‘# Send email’ part, but this should give you the results you are looking for. $result | Where-Object {$_.DaysSince -gt 2}
For some reason the forum is not displaying my formatting (indenting or spacing) correctly.
# Get server names
$server = Import-Csv $ServerList |
Select-Object -ExpandProperty ServerName
# Get dbs on each server
Import-Module SQLPS
$db = Get-SqlDatabase -ServerInstance $server
# Get db info plus days since last backup
$result =
foreach ($d in $db){
$d | Where-Object {
$_.Name -notmatch 'tempdb|model' -AND $_.RecoveryModel -eq 'Simple'
} | Select-Object Name,Status,RecoveryModel,LastBackupDate,
@{n='DaysSince';exp={((Get-Date) - $_.LastBackupDate).Days}},
@{n='ServerName';exp={$_.Urn.Parent.Value -match "'(.*)'" |
Out-Null ; $Matches[1]}}
}
# Get specific info based on dayssince
# Convert PSCustomObject to string and add to body of email
$emailbody = $result | Where-Object {$_.DaysSince -gt 2} |
Format-List | Out-String
# Send email
$mail = @{
From = 'you@mail.com'
To = 'you@mail.com'
Subject = 'Old SQL Backups'
Body = $emailbody
SmtpServer = 'your.mailserver'
}
Send-MailMessage @mail
A "good backup" would say the database was backed up today or yesterday.
… Oook… how do you know that backup is not corrupted?
In disaster recovery scenarios that I regularly find myself in. The number of times, I have been told, by a customers team that the DB backups or file server backup are fine. I’ve lost count. Only to discover, when I am helping them recover, that the last 3 - 9 are all bad. So, not they are at a 90+ days data loss.