Why can't a differential backup specify its base?

Question

This is my first DBA.SE post, so please inform me of any mistakes, thanks!

I am a new DBA (not an IT pro, just no one else in the company to do it), so the more basic the explanation the better. I have been reading about database backup strategies (or, as I have learned to call them, "restore strategies"). I understand what Full, Differential, and Transaction Log backups do, but I want to know why a differential backup can only be based on the most recent full backup.

If a differential backup is everything that has changed since the last full backup, then why can't the differential be based off of any backup of my choosing? To be more clear, I'm asking about specifying the base when the backup is taken, not when restoring. I am assuming that when restoring you would choose the correct base and corresponding differential to perform the restore (not using a differential made from base B to restore from base A).

What is the reason that prevents this functionality from being possible? I figure that there must be a reason, I just don't know what it is.

Note: I understand that the base cannot be specified, but my question is why not? (I'm also not interested in discussion about "why would you?")

Analogy

Here's an analogy for how I understand a differential backup:

I have an Excel file with some data in cells.

On day 1, I make a copy of this file and store it somewhere else (the "full backup").

On day 2, I look at the file and compare it to the backup copy that I made on day 1, and I note all the cells that have changed and what their new values are (a "differential backup"). I am not noting every change made to a cell, only what its final value is. If cell A1 started as "Alfred", changed to "Betty", "Charlie", then "Dave", I would only note that "A1 is now Dave".

On day 3, I compare the current file with the backup file again and note the changes (another "differential backup" with the same base as day 2). Again, only noting final values per cell at the time observed, not all values that the cell has been throughout the day.

On day 4, I compare again and note changes again. Continuing with cell A1, now it says "Sarah", even if it was 10 other names throughout the day, and all I note is "Now A1 is Sarah".

On day 5, my file gets messed up; so, I look at the backup copy that I made on day 1, then the final states noted on day 4, and I apply the changes noted to the backup copy and now I have the file "restored" to how it was on day 4. So, I look at the backup made on day 1, see that on day 4 cell A1 ended as "Sarah", and change the backup cell A1 to be "Sarah".

Why would it matter if I had made another backup copy ("full") of the file on day 2? Why wouldn't it still be possible to compare (read, "take a differential backup of") the file on day 3 or 4 with the copy made on day 1? As I understand it, SQL Server would require me to compare (when taking another differential backup) to a full backup made on day 2 (if one had been made)- no other option.

Answer 1

A differential backup uses what is called the differential change map to build a list of pages that have been modified since the last full backup. This list is a "differential" list, hence the name of the backup type, and the reason the backup can only ever be restored over top of the associated full backup.

Performing a full backup resets the differential change map. From that point forward, any page that is modified is recorded in the map. If you then take a differential, that backup only contains pages that have been modified since the last full backup, and recorded in the map.

In your analogy, the two full backups, which serve as a base for the entire restore process would likely have different contents, and therefore different differential maps. If you restore a diff based on the first backup over the 2nd backup, the database would likely be corrupted. In fact, SQL Server prevents the restore of a diff backup over anything except the original full backup it is based upon.

When you ask SQL Server to take a differential backup, the only "base" for the differential is the single differential change map present in the database at the time the differential backup starts. This is why you cannot specify the base for the differential backup.

---

In response to a comment from @MartinSmith - you might be able to use COPY_ONLY backups to restore a differential backup over a number of full backups. Consider the following scenario:

1. BACKUP DATABASE xyz TO DISK = 'path_to_backup.bak';
2. BACKUP DATABASE xyz TO DISK = 'path_to_backup_2.bak' WITH COPY_ONLY;
3. BACKUP DATABASE xyz TO DISK = 'path_to_backup_3.bak' WITH COPY_ONLY;
4. BACKUP DATABASE xyz TO DISK = 'path_to_backup_4.bak' WITH COPY_ONLY;
5. BACKUP DATABASE xyz TO DISK = 'path_to_backup_diff.bak' WITH DIFFERENTIAL;

The differential backup in step 5 should be capable of being restored over any of the backups taken in steps 1 to 4, since the differential change map is only cleared when the full backup in step 1 occurs. The COPY_ONLY backups in steps 2, 3, and 4, do not reset the change map. Since the differential change map accumulates changes made since the full backup, each of the successive COPY_ONLY backups contains enough information for the differential backup to work against any of the previous 4 backups.

Although it seems like it should work, in practice, restoring a differential over top of a copy_only backup results in the following error:

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I've created a SQL Server 2012 platform repro for testing differential and copy_only restores, and saved the file on gist.github.com - WARNING the script will drop any database named RestoreTest as its first step.

Answer 2

The feature you want could exist in principle. It would not be efficient with the current database structures (see Max Vernon's answer). SQL Server would have to either maintain a set of diff maps or compare the current DB contents against the full backup that you specify as a base.

There are applications that deduplicate large files. You can make two full backups and only the changed data will actually be stored. This is like a diff with custom base. exdupe for example can do that.

The nice thing about that is that it works with any set of backup files at all. In fact starting with the 3rd full backup file you will only pay incremental (not differential) space usage. The space usage is the difference to the previous backup file (not to the first). Deduplicating storage have similar behavior.

Why does the feature that you describe not exist? Each feature consumes budget causing other features to not be present. This one apparently did not make it far enough on the priority list. I'm not sure what it would be good for. Seems like a fairly esoteric requirement to use custom bases.

Answer 3

Don't confuse transaction log backups with differential backups, they have different purposes! What you're calling a "differential backup", whereby you "note all the changes to the cells", is in fact a transaction log.

A differential backup's purpose is to keep the size of the resulting backup file small by only recording the information that has changed since the last full backup, and to keep the restore time within your recovery time objective (RTO).

A transaction log backup's purpose is to let you replay the transactions to an arbitrary point in time - often, but definitely not necessarily to "the most recent anything to happen".

What you're talking about is in fact possible - but you need to restore the full backup, and then restore the transaction logs.

If you have the day 1 full backup and all of the transaction log backups between day 1 and day 5, there's nothing stopping you from restoring the day 1 backup and replaying the transaction log until you have the data as it was on day 4. You could also start from the day 2 backup, which would be slightly faster to restore, as you'd be replaying fewer transactions. You could also restore the day 1 full backup, the day 3 differential backup, and then restore the transaction logs to day 4.

Edit: OK, your edited analogy makes a little more sense. The answer is then "because you can already achieve what you want with transaction log backups". A differential backup is merely a cheap and convenient way of recording a whole bunch of transaction log activity. It doesn't offer any data recovery granularity that a transaction log backup doesn't offer. There's only so many features that offer "mere convenience" that make it into a product.

Answer 4

Giving an analogy with Excel is comparing apples and oranges. Why ? Excel is not a database as it lacks data integrity. Excel is a pretty nice spreadsheet application and might be a complement to database.

SQL Server is a relational database system which allows you to store all your data and provides a mechanism to query it. The important part is "Relational" as data relationship is important along with data integrity (ACID properties).

Basics :

The data in the database is organized into logical components (tables, views, procs, triggers,etc) that is visible to user. At minimum, a database is also physically implemented as two (data & log file) or more (secondary data file) files on disk.

• A database contains page which is the fundamental unit of data storage used to store records.
• A database page is an 8192-byte (8KB) chunk of a database data file.
• 8 physically contiguous pages (8*8KB = 64KB) in a database file forms an extent.
• An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called GAM intervals.

why a differential backup can only be based on the most recent full backup. -- or -- If a differential backup is everything that has changed since the last full backup, then why can't the differential be based off of any backup of my choosing?

Based on your analogy about excel, what you are doing is applying what has changed to the former. This is applying all committed transactions in from transaction log with STOP AT (note: On day 5 the file gets messed up and you are stopping at day 4)

In every 4GB section (called a GAM interval) of every data file there is a special database page called a differential bitmap that tracks which portions (called extents) of that 4GB section have changed since the last full backup, indicating data that has changed or been added to the database.

A differential backup scans through these bitmaps and only backs up the data file extents that are marked as changed. The bitmaps are reset by the next full backup (hence a differential backup can only be based on the most recent full backup), so you can see that as more and more of the database changes, more of it will be marked in the differential bitmaps and successive differential backups will be larger and larger.

You can even use this script to find out How much of the database has changed since the last full backup?.

The differential base information is stored in master database - sys.database_file or (sys.master_files - useful when database is read_only or offline).

There are 3 important columns that store info related to differential base.

• The differential_base_lsn is the base for differential backups. The data extents that are changed after differential_base_lsn will be included in the differential backup.
• The differential_base_guid is the unique identifier of the base backup on which a differential backup is based.
• The differential_base_time is the time that corresponds to differential_base_lsn

A differential backup is useful to speed up the RTO (Recovery Time Objective = Time it takes to recover your database) as opposed to more frequent full backups which will be a problem for large databases or restoring volume of transaction log backups as they could grow large over time frame.

Note: A COPY_ONLY full backup does not reset the differential base, so a COPY_ONLY backup cannot serve as a differential base.

References :

• Anatomy of a page
• Anatomy of a record
• Understanding SQL Server Backups