Detecting backup failure via PowerShell or SQL?

RonJohn

Hi,

RG Backup 6.4.0.56
SQL Server 2008 R2

Back in October, I was helpfully shown how to list all backup history using an SQL query. Thank you, Manfred Castro.

http://www.red-gate.com/MessageBoard/viewtopic.php?t=15972

SELECT	CAST(BUS.DATABASE_NAME AS VARCHAR(30)) AS DB,
	CASE BUS.[type]
		WHEN 'D' THEN 'Full'
		WHEN 'I' THEN 'Incr'
	END as BU_Type,
	CAST(BUS.BACKUP_START_DATE AS CHAR(18)) AS START_TS,
	DATEDIFF(second, BUS.backup_start_date, BUS.backup_finish_date) AS WALL_SECS,
	CAST(BUS.backup_size / 1000000 AS INT) AS SIZE_MB,
	BUS.first_lsn,
	BUS.last_lsn,
	CAST(BUS.recovery_model AS VARCHAR(8)) AS RECOV_MODEL,
	CAST(BMF.physical_device_name AS VARCHAR(128))
FROM master..sysdatabases SD
	LEFT OUTER JOIN msdb..backupset BUS ON SD.name = BUS.database_name,
	MSDB..backupmediafamily BMF
WHERE CAST(BUS.BACKUP_START_DATE AS DATE) = CAST(GETDATE() AS DATE)
  AND BUS.[TYPE] IN ('D', 'I')
  AND BMF.media_set_id = BUS.media_set_id
  AND BMF.FAMILY_SEQUENCE_NUMBER = 1
ORDER BY BUS.DATABASE_NAME

,

However, I assumed that when a backup failed that it would not update MSDB..BACKUPSET.BACKUP_FINISH_DATE or LAST_LSN.

Sadly, that's not the case.

So, is there a way (and where can I find the documentation) to automate the detection of failed backups?

Sincerely,
Ron