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 [1] = 7 days ago
results [7] = 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],
[1],[2],[3],[4],[5],[6],[7]
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.

Report1

Opening up a Server shows the results for 7 days.

Report2

You can download the file here if you’re interested 🙂

http://sdrv.ms/Hkzuwe

Advertisements