SSRS Report on % Diff since last full backup
After getting Pauls awesome little function into PBM (https://sqlnotesfromtheunderground.wordpress.com/2013/10/21/pbm-difference-since-last-backup/) and having it run a while, i needed to start looking at the results. And seeing where i can make some changes to our backup strategy.
I set up EPM (http://epmframework.codeplex.com/) which puts all the results into a database (mdw). It was fun working out how to parse out the database name and results from xml . Im sure the query isn’t the finest but it gets the job done. I only use the last seven days of data then pivot it to make it pretty.
/* Results for % Diff Since Last Backup Job Standard 30% diff = Full Backup needed nightly results  = 7 days ago results  = last nights */ WITH BackupDiffStd ([rank],[server],[database],[result]) AS ( SELECT RANK() OVER (partition by EvaluatedObject ORDER BY EvaluationDateTime) AS 'Rank', EvaluatedServer , REVERSE(LEFT(REVERSE(EvaluatedObject), CHARINDEX('\', REVERSE(EvaluatedObject)) - 1)) AS 'database' , --PolicyResult , CAST(ResultDetail.query('/Operator/Function/ResultValue/text()') AS VARCHAR(5)) AS '% Diff' FROM policy.PolicyHistoryDetail WHERE CategoryName = 'Backup_Details' AND EvaluationDateTime > GETDATE()-7 AND EvaluatedObject <> 'No Targets Found' ) SELECT server , [database], ,,,,,, FROM BackupDiffStd PIVOT (max(result) FOR RANK IN (,,,,,,)) AS result ORDER BY server, [database]
For the report I’ve just grouped by server name and done some basic background colour changes to highlight days that have changes over 30 and 50.
Opening up a Server shows the results for 7 days.
You can download the file here if you’re interested 🙂