If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Differential backups - under the covers

I have a question about how differential backups actually work in SQL Server 2000. Before I go any further, let me state that I do understand very well that a Differential backup records all the changes to the database since the last full backup. However, I would be interested to know how this is actually done by SQL server? The issue that puzzles me is that the Transaction Log is the normal place where you would expect to find the history of transactions necessary to do the differential backup. However, I think it would also be true to say that the history of transactions can be cleared down as a result of checkpoints/truncations. This would lead to the situation where the Transaction Log file would be truncated - losing some of the history that would otherwise provide the transaction history since the last full backup.

Therefore, when doing a differential backup that may be only one in a long sequence of differential and transaction log backups, how does SQL Server find out the transaction history since the last full backup when the transaction log does not necessarily contain all this information?

I believe that a full backup writes to each page header in the database which LSN (or similar counter) was last picked up by the full backup. A differential backup reads this counter, and figures out if the page has been written to since the last (full of diff) backup. If the page has been written to, then the page is backed up. If the page has not had the header updated, then page is assumed to be the same as it was before, and not backed up. You are correct that in order ot get a real point in time recovery you need the transaction logs. Differential backups are just a way of not backing up historical/static data that does not change day to day. Clear as mud?

Yes, I can understand what you're saying. So, you're suggestion is that it scans every page in the database. Maybe another answer (I picked it up from another forum and a note can be found in BOL) that SQL Server scans all the previous backup files and finds out what has changed in that way.

note from BOL:-

"If you have created any file backups since the last full database backup, those files will be scanned by Microsoft® SQL Server™ 2000 at the beginning of a differential database backup. This may cause some degradation of performance in the differential database backup. For more information, see Using File Backups."