Red Gate forums :: View topic - Detecting backup failure via PowerShell or SQL?

Go to product support center

SQL Backup 6 forum

Detecting backup failure via PowerShell or SQL?

Search in SQL Backup 6 forum

Jump to:

Author

Message

RonJohn

Joined: 17 Oct 2012
Posts: 3

Posted: Mon Dec 10, 2012 7:55 pm Post subject: Detecting backup failure via PowerShell or SQL?

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 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 Sad

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

Display posts from previous:

	All times are GMT + 1 Hour
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum