Red Gate forums :: View topic - Detecting backup failure via PowerShell or SQL?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

Detecting backup failure via PowerShell or SQL?

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
RonJohn



Joined: 17 Oct 2012
Posts: 3

PostPosted: Mon Dec 10, 2012 7:55 pm    Post subject: Detecting backup failure via PowerShell or SQL? Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic 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


Powered by phpBB © 2001, 2005 phpBB Group