May 23, 2014 at 9:35 am
Hi,
What I thought would be a simple exercise in getting some information from msdb.dbo.backupset has turned into query pudding.
I'm trying to get the database name, 'original' backup size, size after growth, and the date that the growth occurred.
Here's the query as I have it (re-using the heck out of some Jeff Moden code). It seems to be getting me close, but I think I'm missing something as there are too many matches.
SELECT t.database_name
, t.backup_size
--, t.backup_start_date
, ca.backup_size_growth
, ca.backup_growth_date
FROM msdb.dbo.backupset t
CROSS APPLY (SELECT TOP 1 ta.backup_size, ta.backup_start_date
FROM msdb.dbo.backupset ta
WHERE ta.database_name = t.database_name
AND ta.backup_size > t.backup_size
AND t.[type] = N'D'
AND t.database_name LIKE N'EDDS[0-9]%'
AND t.backup_start_date > CAST('2014-01-01' AS DATETIME)
GROUP BY ta.backup_size, ta.backup_start_date
ORDER BY ta.backup_start_date) ca (backup_size_growth, backup_growth_date)
WHERE t.[type] = N'D'
AND t.database_name LIKE N'EDDS[0-9]%'
AND t.backup_start_date > CAST('2014-01-01' AS DATETIME)
GROUP BY t.database_name
, t.backup_size
--, t.backup_start_date
, ca.backup_size_growth
, ca.backup_growth_date
ORDER BY t.database_name,ca.backup_growth_date;
May 23, 2014 at 9:36 am
Ah, fooey. Needed to keep the datediff between the two to seven for weekly backups. 😎
SELECT t.database_name
, t.backup_size
--, t.backup_start_date
, ca.backup_size_growth
, ca.backup_growth_date
FROM msdb.dbo.backupset t
CROSS APPLY (SELECT TOP 1 ta.backup_size, ta.backup_start_date
FROM msdb.dbo.backupset ta
WHERE ta.database_name = t.database_name
AND ta.backup_size > t.backup_size
AND t.[type] = N'D'
AND t.database_name LIKE N'EDDS[0-9]%'
AND t.backup_start_date > CAST('2014-01-01' AS DATETIME)
AND DATEDIFF(dd, t.backup_start_date, ta.backup_start_date) = 7
GROUP BY ta.backup_size, ta.backup_start_date
ORDER BY ta.backup_start_date) ca (backup_size_growth, backup_growth_date)
WHERE t.[type] = N'D'
AND t.database_name LIKE N'EDDS[0-9]%'
AND t.backup_start_date > CAST('2014-01-01' AS DATETIME)
AND DATEDIFF(dd, t.backup_start_date, ca.backup_growth_date) = 7
GROUP BY t.database_name
, t.backup_size
--, t.backup_start_date
, ca.backup_size_growth
, ca.backup_growth_date
ORDER BY t.database_name,ca.backup_growth_date;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply