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!