Detecting backup failure via PowerShell or SQL?

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Detecting backup failure via PowerShell or SQL?

Postby RonJohn » Mon Dec 10, 2012 7:55 pm

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

Code: Select all
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
RonJohn
 
Posts: 3
Joined: Wed Oct 17, 2012 5:28 pm

Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests

cron