August 24, 2012 at 4:32 am
--Getting NULL output against the column : completion_status
select a.name, case b.type
when 'D' then 'Full Database Backup'
when 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'Log Backup'
END AS Backup_Type,
max(b.backup_finish_date) LastSuccessfulBackup,
cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) as 'IntervalInDays',
case
when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 1 then cast('X' as varchar(10))
when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 7 then cast('Y' as varchar(10))
End as completion_Status
from master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b
ON a.name = b.database_name
where a.name not in ('tempdb')
group by a.name, b.type
order by a.name, b.type
I need to get the records in the completion_status column as follows:
1. If the IntervalInDays is > 1, then I should be able to print "full db completed against the column(completion_Status),
2. If it is for more than 7 days, it should be available as "diff bkp completed"
Thanks.
August 24, 2012 at 4:49 am
You need to handle all eventualities once and once only in your case expression - something like this:
...
CASE WHEN Interval < 1 THEN 'X'
WHEN Interval >= 1 AND Interval < 7 THEN 'Y'
WHEN Interval >= 7 THEN 'Z'
ELSE 'Warning - Interval falls outside expected ranges'
END
...
John
August 24, 2012 at 7:28 am
John Mitchell-245523 (8/24/2012)
You need to handle all eventualities once and once only in your case expression - something like this:
...CASE WHEN Interval < 1 THEN 'X'
WHEN Interval >= 1 AND Interval < 7 THEN 'Y'
WHEN Interval >= 7 THEN 'Z'
ELSE 'Warning - Interval falls outside expected ranges'
END
...
John
Actually, you're wrong on both counts. You do not need to handle eventualities at all. The CASE expression will just produce a NULL if you do not handle the eventuality. I use this quite a bit, especially when the CASE expression is embedded in a COUNT(). It doesn't make sense to me to code "ELSE NULL" when it's going to do that anyhow.
Second, you do not need to make sure that each eventuality meets only one condition. The CASE expression short circuits the evaluation once a condition is met. That means that you can simplify your conditions, because you don't need to exclude previous conditions, because that condition won't even be evaluated if previous conditions are met. Your code can be simplified as follows:
...
CASE WHEN Interval < 1 THEN 'X'
WHEN Interval < 7 THEN 'Y'
WHEN Interval >= 7 THEN 'Z'
ELSE 'Warning - Interval falls outside expected ranges'
END
...
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 24, 2012 at 8:15 am
Point taken, Drew - I should have phrased it better and said "your code would be clearer if" rather than "you need to". You're right that there's no syntactical requirement to do it that way, and that overlapping conditions are allowed. However, the overlapping conditions in the original post meant that the second condition would never have been evaluated be evaluated as True.
Your suggestion of allowing a NULL to be returned isn't an option here since the very reason for this thread is that NULLs are not wanted. Coding an ELSE NULL is indeed pointless, which is why I didn't suggest it.
Perfectly happy with the way you rewrote my code, by the way!
John
August 24, 2012 at 2:03 pm
A small point, but wouldn't the code by simplified by using the DateDiff function to compare the last backup date to today's date? There's no need to convert from datetime to numeric.
Cheers,
Elliott
August 25, 2012 at 1:11 am
Hi Guys,
I have wholly modified the script as below. Please do execute this and suggest if this is looking fine now.
select a.name, case b.type
when 'D' then 'Full Database Backup'
when 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'Log Backup'
END AS Backup_Type,
max(b.backup_finish_date) LastSuccessfulBackup,
cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) as 'IntervalInDays',
case
--when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 1 then cast('Completed' as varchar(10))
--when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 7 then cast('Failed' as varchar(10))
when datediff(hh,max(b.backup_finish_date),getdate()) > 1 then cast('Completed Full BKP' as varchar(30))
when datediff(hh,max(b.backup_finish_date),getdate()) < 1 then cast('Failed Diff BKP' as varchar(30))
End as completion_Status,
case
when (max(b.backup_finish_date) is NULL )then 'Backup Failed-no Data Found'
end as backup_status_data_not_found
from master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b
ON a.name = b.database_name
where a.name not in ('tempdb') --and b.type = 'D'
group by a.name, b.type
order by a.name, b.type
Thanks.
August 25, 2012 at 10:16 am
Hi Sourav,
Glad that lyou are making progress towards your goal.
I don't have SQL Server open in front of me, so these comments are just observations, maybe correct, maybe not.
It looks like you are measuring success if the latest backup time is within one hour of the current time. Is this what you want, and does it hold for all types of backups (full, diff, log)?
Also, the query looks for backup types where the max(backup_finish_date) is null and that condition is called "No data". That would mean that no backup had ever been completed for that database (and type). Since that condition is fairly unusual (in my experience), you may want to add additional code to your solution that defines a failed backup event as conditional based on the type of backup and the length of time between the current time and the last backup of that type (e.g. >24 hours means failed full backup; >1 hour means failed diff backup, etc)
I think you are going in this direction, but perhaps you didn't post your full code.
HTH,
Elliott
August 28, 2012 at 2:37 am
Sourav
I'm not quite sure what you're doing with your completion_Status column. You're returning 'Completed Full BKP' if the backup (of any type) is more than an hour old, and 'Failed Diff BKP' if it's less than an hour old. Is this really what you intend? You don't handle the case where the DATEDIFF function returns 1 - is that intentional? Finally, you should consider rewriting your code so that the date arithmetic only neeeds to be carried out once (instead of once per row) and so that any indexes on the columns can be used. The example below doesn't do exactly the same thing as yours (it's more precise since DATEADD deals in precise dates rather than number of boundaries crossed) so you'd want to test that it works for you.
...
when dateadd(hh,-1,getdate()) > backup_finish_date 1 then cast('Completed Full BKP' as varchar(30))
...
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply