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?")
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.