Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Differential backups use the Differential Changed Map pages to determine what pages to backup. I'd like to investigate a backup strategy involving differential backups. For that purpose I'd like to automatically track the amount of changed data over time for multiple databases.

I'd like to programmatically query the Differential Changed Map to determine how many pages are changed.

This seems to be possible with DBCC PAGE according to http://www.practicalsqldba.com/2013/07/sql-server-understanding-differential.html. Is there a cleaner or "more documented" way than this? It seems like this will be awkward and error-prone to implement.

share|improve this question
    
@PaulWhite that's a good start, thank you. I'll leave the question open to see if someone comes up with a cleaner solution but I suspect there is none. Feel free to submit this as an answer. –  usr Feb 1 at 12:48

1 Answer 1

up vote 7 down vote accepted

There's no other way I know of than using DBCC PAGE.

This is the method Paul Randal chose to use when writing a script to predict the size of the next differential backup. Given that Paul headed up the Storage Engine team for many years, I'd think he'd know if there were a better or more documented way.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.