September 30, 2013 at 6:53 am
Hi SQL Masters,
Greetings!
Do you have a script that will determine if the backup failed/succeeded?
Sample output:
Database Name Status Log Date
--------------- ---------- --------------
AdventureWorks2008 FAILED 20130125
AdventureWorks2008 SUCCEEDED 20130126
Something like that.....
Any ideas SQL Masters?
Thank you and Best Regards,
September 30, 2013 at 8:54 am
You could create a date table/CTE and join every day to the msdb..backupset table on the backup_finish_date to make sure that a backup (of whatever type you choose) was taken successfully.
If you are using scheduled jobs to run your backups you can write a query to return all successes and failures of that job.
So it depends are what you are auditing exactly.
declare @BeginDate datetime = '20130923'
,@EndDate datetime = '20131001';
with BackupDate as
(
SELECT
@BeginDate BackupDate
UNION ALL
SELECT
DATEADD(day, 1, BackupDate) BackupDate
FROM
BackupDate
WHERE
BackupDate < @EndDate
)
select
distinct
bd.BackupDate
,d.name
, bs.[type]
,case when backup_set_id is not null then 'Backup Completed'
else 'No Backup' end
from BackupDate bd
cross apply master.sys.databases d
left join msdb..backupset bs on bd.BackupDate = convert(varchar(10), bs.backup_finish_date, 112)
and bs.database_name = d.name
--and bs.database_name = '' --If you want to specify a single database
--and bs.[type] = ''
option (maxrecursion 0)
September 30, 2013 at 9:13 am
Keith Tate (9/30/2013)
If you are using scheduled jobs to run your backups you can write a query to return all successes and failures of that job.
That is what I do for all scheduled jobs, not just backups.
If any job fails (or step within a job), it gets monitored every hour and it send me an email. That makes it easier to monitor jobs during off hours. A quick glance at my email on my smartphone.
We have some jobs with 10 steps, and if step 5 fails, we still want the other steps to complete. The problem is the job shows as successful, so I need to look at each step for failure, not the overall job.
set @RecCount = 0
set @RecCount = (SELECT count(*)
FROM [msdb].[dbo].[sysjobhistory] h
join msdb..sysjobs j on j.job_id = h.job_id
where run_date = convert(varchar(8),getdate(),112) -- Only Today's jobs
and run_status <> 1 -- not succeeded
and h.step_id > 0-- only look at actual steps
if @RecCount > 0 begin
Build & Send HTML email ....
October 1, 2013 at 4:08 am
I used Policy Based Management as a secondary check on backup age. It's really easy to set up and maintain. You can read an introduction[/url] about it here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2013 at 4:42 am
Hi SQL Masters,
Thank you very much! for your responses, i appreciate it much 🙂
Thank you and Best Regards,
Tsinelas
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply