September 15, 2011 at 5:58 am
Hi there!
This is probably an easy one, but I just can't seem to figure it out! Here it is in pseudo code:
"Get the latest full backup size (type = 'D') and then get the latest differential backup size (type = 'I')
FROM msdb.dbo.backupset and group it by database_name"
Expected result:
DbName FullBckSize DiffBckSize
AW2008 5067700 65084
.. .. ..
I got it working by using backup_finish_date > '2011-09-12', and since there hasn't been any full backups after that, I know that they are the latest backups, but that doesn't feel like the correct way to go! I've tried using MAX(backup_set_id) together with JOINs, CTEs and subqueries, but I can't get the last pieces together....
Very thankful for help!
Sincerely, Gord
September 15, 2011 at 6:42 am
How's that:
WITH latest_backup_CTE
AS ( SELECT database_name ,
type ,
backup_size ,
ROW_NUMBER() OVER ( PARTITION BY database_name, type ORDER BY backup_finish_date DESC ) Rn
FROM msdb.dbo.backupset
WHERE type IN ( 'D', 'I' )
)
SELECT database_name ,
MAX(CASE WHEN type = 'D' THEN backup_size
ELSE NULL
END) AS FullBckSize ,
MAX(CASE WHEN type = 'I' THEN backup_size
ELSE NULL
END) AS DiffBckSize
FROM latest_backup_CTE
WHERE Rn = 1
GROUP BY database_name
ORDER BY database_name
To understand how it works, I'd look up ROW_NUMBER in BOL and crosstabs (it's a form of pivot really) from Jeff Moden's article:
September 15, 2011 at 7:15 am
Thank you so much, Howard, it worked great!
I also want to get the percentage, which is done with this formula: DiffBckSize/FullBckSize * 100.0
When doing this a NULL is returned in the result set for the master database, which you cannot perform a differential backup on. This is OK, but in the Messages tab you get this info:
"Warning: Null value is eliminated by an aggregate or other SET operation."
Is there another way that one could do this, without getting the warning?
Is there another way to get the percentage, without using the MAX(CASE ...) scenario?
I'll look into Jeff's article, as you suggest.
Thank you, once again!
September 15, 2011 at 7:23 am
Just divide the two aggregate functions. e.g. :
CASE WHEN MAX(CASE WHEN type = 'D' THEN backup_size
ELSE NULL
END) = 0 THEN CAST(NULL AS DECIMAL(6, 2)) --Not sure if backup size can be zero, but should protect against divide by zero error
ELSE CAST(MAX(CASE WHEN type = 'I' THEN backup_size
ELSE NULL
END) / MAX(CASE WHEN type = 'D' THEN backup_size
ELSE NULL
END) * 100.0 AS DECIMAL(6,2))
Edit: Sorry, I think you'll always get that warning with a crosstab solution (even the original one) as it's intentionally eliminating NULL's - you can turn the warning off, but it is just that, it doesn't cause any issues
September 15, 2011 at 7:35 am
OK, thanks again!
Yeah, I got the correct percentage before the last post, but thought that there might be another way than dividing the two aggregations.
The warning is something I can live with, but thanks for taking your time! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply