Finding the exact differential backup size in SQL Server

A common question among DBAs is to find exact number of pages would be backed up during a differential backup.

We now have a solution to find the total number of pages which would be backed up during differential backup! This solution is availbale from SQL Server 2016 SP2. This can be done in using sys.dm_db_file_space_usage. Let's see how this is done using a new database

Create database sample

Now run the below query to find the total number of pages on sample database

use sample

go

select total_page_count from sys.dm_db_file_space_usage

go

Creating tables and inserting data into the sample table.

create table empdetail (EmpName char(8000));

go

INSERT INTO EmpDetail values('Thiru');

GO 1000

Now again run the below query to find the total number of pages in database.

use sample

go

select total_page_count from sys.dm_db_file_space_usage

go

Understanding sys.dm_db_file_space_usage

Let us run the below query to understand sys.dm_db_file_space_usage. Below output shows total number of pages in database and total number of pages modified after full backup. If you notice zero, then full backup was not performed or no change has occurred after the last full backup.

Perform full backup to understand the above example

BACKUP DATABASE [SAMPLE] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\sample.bak' WITH NOFORMAT, NOINIT, NAME = N'sample-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

As the next step, we are going to insert some sample records and show you the total number of pages modified after full backup.

INSERT INTO empdetail values('Arasu')

go 1000

Now run the sys.dm_db_file_space_usage to get total number of pages modified after full backup

Now you can easily find the total number pages which would be backed up during differential backup. In the above example, 1070 pages will be backup during the differential backup. The value of modified_extent_page_count will be reset during the full backup.