SQLPS

This next script can be used to check if Full and Transaction Log backups are made within the last two days. Databases are read from a CSV file. I use a slightly modified version which reads databases from a registration database.

# ********************************************************************
# Scriptnaam: CHECKBACKUP.PS1
# Geschreven_door: Tim van Kooten Niekerk
# Versie: 20110811A
# Info: Check if backup is made within last two days...
# ********************************************************************

# Global config settings...
[string]$sServerInstanceFile = ".ServerInstance.csv"
[string]$sSMTPServer1 = "smtp-server-name"
[string]$sToAddress1 = "email-adsress"

# Global objects...
$oNETIP = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()

# Register Snapins...
$oPSSnapinRegResult = PSSnapin SqlServerCmdletSnapin* -registered
if ($oPSSnapinRegResult) {
  $oPSSnapinResult = PSSnapin $oPSSnapinRegResult.Name
  if (-not $oPSSnapinResult) { 
    Add-PSSnapin $oPSSnapinRegResult.Name
  }
} else {
  break
}
  
# Functions and Procedures...
function fSendMail([string]$sSMTPSrvrName, [string]$sFrom, [string]$sTo, [string]$sSubject, [string]$sBody, [string]$sAttFileName) {
  if (($sSMTPSrvrName) -and ($sFrom) -and ($sTo) -and $sSubject) {
  
    # Declare mail objects...
    $oMessage = new-object Net.Mail.MailMessage
    $oSMTP = new-object Net.Mail.SmtpClient($sSMTPSrvrName)

    # Pass values to object...
    $oMessage.From = $sFrom
    $oMessage.To.Add($sTo)
    $oMessage.Subject = $sSubject
    if ($sBody) { $oMessage.Body = $sBody }

    # Add attachment...
    if ($sAttFileName) {
      $oAttachment = new-object Net.Mail.Attachment($sAttFileName)
      $oMessage.Attachments.Add($oAttachment)
    }

    $oSMTP.Send($oMessage)
    if ($? -eq "True") {
      return "INFO: Email has been sent."
    } else {
      return "ERROR: Error sending e-mail."
    }

  } else {
    return "ERROR: Argument(s) missing. [fSendMail `"SMTPServerName`" `"From`" `"To`" `"Subject`" (`"Body`") (`"AttachFileName`")]"
  }
}

function fSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" `
                               + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; `
                               if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

# Start Main script...
[int]$iCounter = 0
Import-Csv $($sServerInstanceFile) -header ("instance", "checkfull", "checktl") | foreach {
  # Skip header...
  if (($iCounter -ne 0) -and ($_.instance)) {
    $oResult1 = $null
    $oResult2 = $null
    # Run Queries on servers...
    if ($_.checkfull -eq 1) {
      [string]$sQuery1 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentFullBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE ((msdb..backupset.type = 'D' OR msdb..backupset.type = 'I') `
      AND msdb..backupset.is_copy_only = 0 `
      AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.state = 0 `
      AND sys.databases.name  'tempdb' `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult1) = fSQLCmd $($_.instance) $sQuery1 "master"
    }


    if ($_.checktl -eq 1) {
      [string]$sQuery2 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentTLBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.recovery_model != 3 and sys.databases.state = 0 `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult2) = fSQLCmd $($_.instance) $sQuery2 "master"
    }

    # Zodra er rows worden geteld een e-mail samenstellen...
    if (($oResult1.Count -gt 0) -or ($oResult2.Count -gt 0)) {
      $oResult1 = $oResult1 | Out-String
      $oResult2 = $oResult2 | Out-String
      [string]$sFromAddress1 = "noreply@" + $oNETIP.HostName + "." + $oNETIP.DomainName
      [string]$sBody1 = $oResult1 + $oResult2
      [string]$sSubject1 = $($_.instance.Replace("", "_")) + ":Database backup older than 2 days..."
      fSendMail $sSMTPServer1 $sFromAddress1 $sToAddress1 $sSubject1 $sBody1
    }
  }
  $iCounter++
}
# ********************************************************************

CSV File format:

instance,checkfull,checktl
SERVERA,1,1
SERVERBINS1,1,1
SERVERC,1,1