It has become widespread knowledge far beyond Database Administrator (DBA) circles that one of the best ways to safeguard against data loss, corruption, and disasters - both man-made and natural - is by performing backups on a regular basis. The most common backup types are a full, incremental and differential. In particular, differential backups have played an increasingly important role in the backup policies of businesses, especially for those running large databases. One of the challenges presented by differential backup is that it can be difficult to determine how much data has changed since the last full backup. Answering this question is crucial in deciding whether to take a Full or Differential backup. In this tip, we will see how SQL Server 2017 helps solve this problem.

Backup Types Explained

Before we get into the specifics of determining how much data has changed since the last full backup, let's take a moment to review the three main types of backup.

Full backups

The most basic type of backup is a full backup. As the name implies, this type of backup copies of all data to another database or storage media. Backing up the entire data set makes restoring it fairly trivial if the need should ever arise. However, performing a full backup can take a very long time, depending on how much data there is, and requires ample space to store it.

Incremental backups

In an incremental backup, only the data that has changed since the last backup is copied. A timestamp is typically employed and compared to the timestamp of the last backup. Because an incremental backup will only copy data since the last backup, it may be run as often as desired. The benefit of incremental backups is that they copy a smaller amount of data than a full backup. Thus, incremental backups will complete faster, and require less media to store the backed up data.

Differential backups

A differential backup is similar to an incremental one the first time it is performed, in that it will copy all data changed since the previous backup. However, on successive runs, it will continue to copy all data changed since the previous full backup. Thus, it will store more data than an incremental one on subsequent runs, although typically far less than a full backup.

Pages, Extents and Dynamic Management Views (DMVs) in SQL Server

The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into 8 KB pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is to say, SQL Server reads or writes whole data pages. At 8 KB per page, this means SQL Server databases have 128 pages per megabyte.

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

DMVs and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. The SQL Server 2017 version of the DMV sys.dm_db_file_spavce_usage has a new column named modified_extent_page_count. This new column shows the number of pages that have changed since the last full backup. For example:

SELECT total_page_count, allocated_extent_page_count
     , unallocated_extent_page_count, modified_extent_page_count
FROM Sys.dm_db_file_space_usage
GO

Here is what running the above query right after the full backup might produce:

If we were now to create a new table and insert a row, when we rerun the query, we would now get a modified output:

You can see that the modified_extent_page_count has gone from 64 to 128. In the next blog, we'll learn how to interpret these results.