February 16, 2009 at 3:59 am
I have noticed that the backup system is very good when it comes to notifications of backups that failed during processing but is there a simple method of identifying if a backup has not been attempted.
For instance, my first thought would be to find out how / if the database logs backups made against it and then to check whether one was done "today" & run that process against all databases on an instance every morning to forewarn me 1st thing.
Any thoughts appreciated.
February 16, 2009 at 4:34 am
Do you mean you're worried that scheduled jobs might not start ? Or that there is no job to start with ? #2 is more likely and you can query the system backup tables to get last backup info.
February 16, 2009 at 4:38 am
I'm comfortable with assuming that people (me I guess) remember to set up appropriate backups for each database so it is just making sure that the backup schedule does trigger (so for instance if the service is down for some reason it will never start) - obviously I'm assuming the service will be up again at some point so I can run a check but I'm just trying to cover all bases.
Thanks
February 16, 2009 at 4:38 am
You can setup a job to run against "backupset"system table in msdb database and setup email notification if the job did not process as required.
February 16, 2009 at 4:57 am
Thanks - have had a quick look and that sounds perfect and simple in that I can just look for any database without a backup in the last 24 hours. Spot on.
February 16, 2009 at 6:20 am
But if the service is down, then your monitor job won't run either.
February 16, 2009 at 6:41 am
I've understood that but that failure will give me a lot of information in itself.
February 16, 2009 at 7:06 am
I'm sure there are plenty of things I haven't considered but this seems to tick the box I need ticking at the moment.
It picks up any databases which are online or offline and have not had a backup today - it ignores databases which no longer exist (detached or otherwise)
create procedure isp_IdentifyDatabasesWithoutBackups
as
begin
selectdistinct database_name
,min(datediff (day, backup_start_date, getdate())) "Days Since Last Backup"
,case
when s.name is null then 'Not attached.'
when s.state = 0 then ''
when s.state = 1 then 'Restoring.'
when s.state = 2 then 'Recovering.'
when s.state = 3 then 'Recovery pending.'
when s.state = 4 then 'Suspect.'
when s.state = 5 then 'EMERGENCY.'
when s.state = 6 then 'Offline.'
else ''
end
frommsdb..backupset b
joinsys.databases s on s.name = b.database_name
group bydatabase_name
,case
when s.name is null then 'Not attached.'
when s.state = 0 then ''
when s.state = 1 then 'Restoring.'
when s.state = 2 then 'Recovering.'
when s.state = 3 then 'Recovery pending.'
when s.state = 4 then 'Suspect.'
when s.state = 5 then 'EMERGENCY.'
when s.state = 6 then 'Offline.'
else ''
end
havingmin(datediff (day, backup_start_date, getdate())) > 0
order by3
end
February 16, 2009 at 7:17 am
That's cool and a good work but if you post it int he Scripts section, many would be benifited from your script and also your name will appear when published 🙂
February 16, 2009 at 7:48 am
thanks for the hint!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply