After getting Pauls awesome little function into PBM ( 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 ( 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 [1] = 7 days ago
results [7] = last nights
WITH BackupDiffStd ([rank],[server],[database],[result])
AS (
RANK() OVER (partition by EvaluatedObject ORDER BY EvaluationDateTime) AS 'Rank',
EvaluatedServer ,
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'
server ,
FROM BackupDiffStd
PIVOT (max(result) FOR RANK IN ([1],[2],[3],[4],[5],[6],[7])) 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 🙂