DMV to check the percentage of a database that has changed since the last full backup - by Ola Hallengren

Status : 

 


74
0
Sign in
to vote
ID 511305 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 11/17/2009 8:42:02 AM
Access Restriction Public

Description

I would like to ask for a DMV to check the percentage of a database that has changed since the last full backup. This could be used in a backup script to change from a differential backup to a full backup if a large percentage of the database has been changed.
Sign in to post a comment.
Posted by Microsoft on 5/1/2017 at 1:50 PM
We have released this in SQL Server 2017. In SQL Server 2017, A new column modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database. The new column modified_extent_page_count will allow DBAs, SQL Community and backup ISVs to build smart backup solution which performs differential backup if percentage changed pages in the database is below a threshold (say 70-80%) else perform full database backup. Please read blog below for more details

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-community-driven-enhancements-in-sql-server-2017/

Regards,
Parikshit
Posted by Microsoft on 12/4/2009 at 9:30 AM
Thank you for this suggestion.
This would indeed be a valuable DMV.
We will consider including it in a future release.
Thanks again,
Kevin Farlee