only report on rows which have a problem

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.

[pre]

Import-Csv $ServerList |ForEach-Object {

$ServerName=$_.ServerName

$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).TotalHours

IF(($Database.Name) -ne ‘tempdb’ -and ($Database.Name) -ne ‘model’)

{

if ($Database.RecoveryModel -like “simple” )

{

$HTML += "

$($SQLServer)

$($Database.Name)

$($Database.RecoveryModel)"

if ($DaysSince -gt 2)

{

$HTML += $($Database.LastBackupDate)

}

else

[/pre]

What do you mean by old and good backups here ?

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

Tagging on to kvprasson’s

'What do you mean by old and good backups here ?'

… point here. They only good backup is one that has been fully tested and validated.

If it is Friday and “old backup” row would say the database hasn’t been backed up since Monday.

A “good backup” would say the database was backed up today or yesterday. I want the report to only list old backups.

-Kevin

As for …

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.

So, just saying… ;-}