Here is the code that I use to get failed backups. You may be able to use it:
CLEAR
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Initialize Arrays
$InstanceList = @()
$NoBackups = @()
# Don't check some databases, such as model and tempdb
$SkipDBs = "model","tempdb","pubs","northwind"
#Retrieve Instance list from database.
$ServerList = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "AdminSQLServerInstance"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $ServerList.Databases.Item("AdminDatabase")
$ds = $db.ExecuteWithResults("SELECT [InstanceName] FROM [AdminDatabase].[dbo].[SQLServerInfo] WHERE AllowConnection = 1 AND CheckBackup = 1 ORDER BY InstanceName")
Foreach ($InstanceNameRow in (($ds.Tables[0]).Rows))
{
$InstanceList += $InstanceNameRow.Item(0)
}
# Loop through all instances
FOREACH ($InstanceName in $InstanceList)
{
Try
{
$Instance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
# Calculate date\time, for date comparison.
# Calculated as 10 AM of the previous day. This is to allow for backups being taken before midnight.
#Get Date from Server where backup job is run.
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $Instance.Databases.Item("master")
$ds = $db.ExecuteWithResults("SELECT DATEADD(HH, -14, CAST(CONVERT(VARCHAR(30), CURRENT_TIMESTAMP, 101) AS DATETIME))")
$CompareDate = (($ds.Tables[0]).Rows[0]).Item(0)
#Retrieves the recovery model and the last backup dates for FULL, DIFFERENTIAL, and LOG backups.
$BackupList = $Instance.Databases | Where-Object {($SkipDBs -notcontains $_.Name)} | Where-Object {$_.Status.ToString().ToUpper() -eq "NORMAL"} | SELECT @{Name='InstanceName';Expression={$Instance.Name}}, @{Name='DatabaseName';Expression={$_.Name}}, RecoveryModel, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate, @{Name='Status';Expression={$_.Status}}
# Loop through each backup, and check for conditions.
ForEach ($Backup in $BackupList)
{
# See if a Full Backup has ever been taken.
IF ($Backup.LastBackupDate -eq "1/1/0001 12:00:00 AM")
{
$NoBackups += $Backup | SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, @{Name='Message';Expression={"Full Backup has never been taken"}}
}
ELSE
{
# Check to see if a Full backup is older than 1 week.
# A full backup may only be taken once a week, if differentials are being used.
IF (($Backup.LastBackupDate -lt $CompareDate.AddDays(-8)))
{
$NoBackups += $Backup | SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, @{Name='Message';Expression={"Full Backup is more than a week old"}}
}
ELSE
{
#Check to see if a Differential or a Full backup has been taken in the previous night.
IF (($Backup.LastBackupDate -lt $CompareDate) -and ($Backup.LastDifferentialBackupDate -lt $CompareDate))
{
$NoBackups += $Backup | SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, @{Name='Message';Expression={"Daily Full or Differential Backup is missing"}}
}
}
}
# Check Log Backup -
IF (($Backup.RecoveryModel.ToString().ToUpper() -ne "SIMPLE") -and ($Backup.LastLogBackupDate -lt $CompareDate))
{
$NoBackups += $Backup | SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, @{Name='Message';Expression={"Transaction Log Backup is missing"}}
}
}
}
catch
{
$BackupObj = "" | SELECT @{Name='InstanceName';Expression={$InstanceName}}, @{Name='DatabaseName';Expression={""}}, @{Name='LastBackupDate';Expression={"1/1/0001 12:00:00 AM"}}, @{Name='LastDifferentialBackupDate';Expression={"1/1/0001 12:00:00 AM"}}, @{Name='Message';Expression={"Failed to retrieve backup data from instance"}}
$NoBackups += $BackupObj
}
}
#$NoBackups
IF ($NoBackups.Count -gt 0)
{
$a = "<style>"
$a = $a + "<!-- "
$a = $a + " TD{font-family: Arial; font-size: 8pt;} "
$a = $a + "--->"
$a = $a + "BODY{background-color:peachpuff;}"
$a = $a + "TABLE{font-family: Arial;font-size: 8pt;width:100%; height:75%; border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"
$a = $a + "</style>"
# Email version for SQL job
# Send Email listing databases that have no backups.
$smtpServer = "smtpServer.yourcompany.com"
$smtpFrom = "FromEmail@yourcompany.com"
$smtpTo = "ToEmail@yourcompany.com"
$messageSubject = "Backup Failures"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Body = $NoBackups | Select-Object @{Name='Instance Name';Expression={$_.InstanceName}}, @{Name='Database Name';Expression={$_.DatabaseName}}, @{Name='Last Backup';Expression={$_.LastBackupDate}}, @{Name='Last Differential Backup';Expression={$_.LastDifferentialBackupDate}}, Message | ConvertTo-HTML -head "<H2>Missing Backups</H2>" -body $a
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}