Joined: 17 Oct 2012
|Posted: Mon Dec 10, 2012 7:55 pm Post subject: Detecting backup failure via PowerShell or SQL?
RG Backup 184.108.40.206
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.
|SELECT CAST(BUS.DATABASE_NAME AS VARCHAR(30)) AS DB,
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,
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,
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?