Hello Friends,

SQL Server 2017 CTP announced with lots of new features to help database professionals. One of that feature is Smart Differential Backup i.e. exposing the modified extent page counts. You can download the SQL Server 2017 in the fastest way as explained by Avanish in his recent post. In this blog post we will learn about the new feature of SQL Server 2017 for Smart Differential Backup.

There are some important points about differential backups:

  1. Differential backup is always based on previous full backup. During the restore scenario, that full backup will work as a base.
  2. Diferential backup contains the data that has been changed since most recent full backup.

Generally, For large size databases the most widely used backup process is like:

  1. Full backup on Sunday midnight.
  2. Differential backup at midnight everyday (Except Sunday).
  3. Log backups based on recovery point objective. It might be every 15 minutes or 30 minutes.

As a database professional, we generally used differential backups to save space or to minimize the number of files to restore (in case of recovery) while using differential with log backups. We generally implement the above explained backup approach on the servers by using maintenance plan, TSQL scripts in Agent Jobs etc.

One important point to think here is the “Diferential backup contains the data that has been changed since most recent full backup”. If lots of data has been changed (lets say more than 80%-90%) since last full backup then differential backup will take almost same space as compare to full backup. In such scenario we can’t save good amount of storage which was one of the reson for implementing differential backup.

With SQL Server 2017 (CTP), a new column modified_extent_page_count has been introduced in sys.dm_db_file_space_usage dmv. Using this column, we can get the information about how much data has been changed since last full backup i.e. Amount of data need to be back up by differential backup.

Step 1: Create a database and table along with some data:

SQL Sever 2017 - Smart Backup Solution

Step 2: Modified some data and check the DMV:

SQL Sever 2017 - Smart Backup Solution

Step 3: Take Differential Backup, value (DataChanged%age) will not be changed:

SQL Sever 2017 - Smart Backup Solution

Step 4: Take Full Backup, value will be changed:

SQL Sever 2017 - Smart Backup Solution

Size of differential and full backup (taken in above step 3 and 4) is as shown below. You can compare the size as well.

SQL Sever 2017 - Smart Differential Backup


Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook