Understanding Full Backup Process in SQL Server: Do All Allocated Extents Get Backed Up?

Question

I've been reading a blog post by Paul Randal on the SQL Server backup process, where he outlines the main phases of a full backup. One point he mentions is that during a full backup, the process reads "all allocated extents, regardless of whether all 8 pages in the extent are in use".

A full backup has the following main phases:

• Perform a checkpoint.
• Read all in-use data from the data files (technically, reading all allocated extents, regardless of whether all 8 pages in the extent are in use).
• Read all transaction log from the start of the oldest uncommitted transaction as of the initial checkpoint up to the time that phase 2 finished. This is necessary so the database can be recovered to a consistent point in time during the restore process (see this post for more details).
• (Optionally test all page checksums, optionally perform backup compression, and optionally perform backup encryption).

This statement has led me to wonder about the specifics of what precisely gets backed up. Specifically, does the backup process include all allocated extents, even if not all pages within these extents are in use?

I'd appreciate any insights or clarifications on this topic. Thank you in advance for your help!

Answer 1

Specifically, does the backup process include all allocated extents, even if not all pages within these extents are in use?

Yes. Extents are the lowest unit of object allocation (technically since 2016 all new databases use uniform extents, though when using shared extents there are single pages allocated) in general for database objects. Regardless, the unit of allocation and thus backup in a Data File is the extent.

You can run a test on this, yourself if you're so inclined, to check the behavior.

I created a test database with nothing really in it, created a table, and put in a single small width row. This will give me a partially filled uniform extent (a single page, actually, so 1 page in use and 7 pages not currently in use and thus reserved).

Notice that the first allocated page for the table (NOT the IAM page) is at 392 which is always the start of an extent. Doing the math, page 392 is at extent 49 (392 page/8 pages in an extent) and on the disk at offset (392 page * 8192 bytes per page) 3211264.

If we do a backup, as noted in your original post, first a checkpoint will happen so that all dirty pages and any needed log blocks are flushed to disk, because the file on the disk is what is going to be read in order to backup the database. This means that we should have reads for that extent and the size of the read should be the size of the extent (64k).

You can see from the picture above, that this is the case. The entire extent is backed up, regardless that only a single page is "allocated". To further prove the point, the same process except for a write can be done for a restore.

You can see from the above, the restore wrote the entire extent (64k) from the extent offset on disk we calculated. Thus, the level to which this is done for typical objects in a database is at the extent level, or 64k.

Note that not all IO will be 64k and there are different control structures which may require different behaviors. This was not the OPs question but put here for the pedants.

Also note that Paul answers this in his original post:

Read all in-use data from the data files (technically, reading all allocated extents, regardless of whether all 8 pages in the extent are in use).