Differential backups getting confused by MS SQL backups?

[Sergiu]

Hello,

I have a question, which has caused a bit of confusion for us: we have a daily differential backup running on an (MS) SQL Server (2000), obviously along with a full weekly backup. However, recently it doesn't seem to be backing up properly.

Normally, differential backups are somewhere between 5 and 25 GB. However, this week they've only been under 1GB. The only thing that has changed between last week and this week is that we now run another backup within SQL Server itself (its own backup feature), for different purposes. Would this affect the way the differential works? Would it trick the differential jobs into thinking that data has already been backed up and therefore it gets skipped, anything to do with transaction logs etc?

Thank you!

edit: in a simpler way, I guess what I mean is along the lines of "if SQL server does a backup itself, does it truncate the logs, which would make BE assume the data has already been backed up and therefore BE doesn't actually do a complete differential backup?" - I think that's a bit clearer :)

Message Edited by Sergiu on 05-02-200702:13 AM

[Hywel_Mallett]

I think, (though don't quote me!), that what you are saying is correct.

[Sergiu]

Thank you (so far) - it's not quite what I was hoping for, but it was the most likely thing!

So, according to the (unofficial) FAQ, "If you use SQL's own backup utility, a full backup will backup the database, then truncate the log files" - so the next question would be "how does a differential SQL backup know what to backup?".

From the MS KB, it sounds like a full backup of a database will reset the Differential Bitmap Page because it essentially creates a new differential base, and therefore when BackupExec comes along to do a differential, it doesn't actually backup what the user intended it to because the real base for the diff backup is not what BE previously backed up itself, but what SQL Server backed up (probably after the BE full backup) - albeit to a backup set that BE knows nothing about.

Anyway... a bit confusing, but it does make sense in a "I don't actually understand databases yet but that sounds plausible" way. That is, unless I've been misled, in which case please do correct me!

Thank you!

Sergiu