only report on rows which have a problem

Welcome Forums General PowerShell Q&A only report on rows which have a problem

This topic contains 5 replies, has 4 voices, and was last updated by

 
Participant
3 months ago.

  • Author
    Posts
  • #145853

    Participant
    Topics: 15
    Replies: 10
    Points: 66
    Rank: Member

    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.

    
    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 
    
    
  • #145892

    Moderator
    Topics: 8
    Replies: 891
    Points: 2,749
    Helping Hand
    Rank: Community Hero

    What do you mean by old and good backups here ?

  • #145902

    Participant
    Topics: 3
    Replies: 264
    Points: 78
    Helping Hand
    Rank: Member

    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
    
    
  • #146249

    Participant
    Topics: 2
    Replies: 968
    Points: 1,784
    Helping Hand
    Rank: Community Hero

    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.

  • #146253

    Participant
    Topics: 15
    Replies: 10
    Points: 66
    Rank: Member

    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

  • #146286

    Participant
    Topics: 2
    Replies: 968
    Points: 1,784
    Helping Hand
    Rank: Community Hero

    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... ;-}

The topic ‘only report on rows which have a problem’ is closed to new replies.