How to check for failed Database Backups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to check for failed Database Backups

Author

Topic

Mexy26
Starting Member

4 Posts

Posted - 2007-05-08 : 14:35:59

Hello everyone! Looking for some insight here on database backups that fail.

We have many SQL servers that we maintain by storing Job/Maintenence Plan history on a central server, which then emails out daily reports to let us know what backed up last night and what didn't

This was easy to do in SQL 2000, not so much in SQL 2005. I have put together a query that gathers the info I need for the successes:

SELECT DISTINCT '00000000-0000-0000-0000-000000000001' AS Plan_ID, mpld.line1 as "Plan Name", bud.database_name as "Database", mpld.server_name, 'Backup Database' as Activity, mpld.succeeded, bs.backup_finish_date, DATEDIFF (MS,bs.backup_start_date,bs.backup_finish_date) as Duration, bs.backup_start_date, mpld.error_number, mpld.error_messageFROM msdb.dbo.sysmaintplan_logdetail mpld
INNER JOIN msdb.dbo.backupset bs
on (select convert(char(12),mpld.start_time,109))=(select convert(char(12),bs.backup_start_date,109))-- on bs.database_name=bud.database_name
inner join
msdb.dbo.bu_dbs bud
on bs.database_name = bud.database_name
WHERE mpld.succeeded = 1 and mpld.line2 like 'Backup%' and bs.type='d'
and bs.backup_start_date > ( SELECT CONVERT(char(12), (GETDATE()-1), 109) )
ORDER BY bud.database_name DESC

But I am having trouble using a query to determine the databases the FAILED during backup. MSDB.BackupSet and MSDB.SYSMaintPlan_LogDetail really have nothing,because often times, even if a step in a Maint. Plan fails, the plan finishes reporting success.

Does anyone know of a good way to gather info about failed database backups?

Thank you!

Mario

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-08 : 14:48:08

Why don't you just check for failed jobs?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Mexy26
Starting Member

4 Posts

Posted - 2007-05-08 : 15:27:38

Just perusing through the System tables and views, but I'm not seeing much in terms of failed jobs.

By checking for failed jobs, will it tell me what database it failed on? What I've found in the past is that the job will complete successfully, even if a database doesn't get backed up, so that might not help me too much.

And I need to do all this programatically through T-SQL.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-08 : 15:35:39

There is status column in msdb..sysjobhistory.

Mexy26
Starting Member

4 Posts

Posted - 2007-05-08 : 15:54:38

Yes, I see the status column, however that just tells me that the job failed or succeeded. It doesn't tell me that 4 of the 5 database backed up just fine. I need to know the 1 database that didn't get backed up.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-09 : 11:14:25

If you use maintenance plan, should check plan's log file.

Kristen
Test

22859 Posts

Posted - 2007-05-09 : 12:55:56

"I need to know the 1 database that didn't get backed up."

Query msdb..sysjobhistory for "gaps", maybe?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources