My question is similar to this one except my full and differentials do not happen as often. I do a full once a week and a differential on weekday evenings. My differential backups at the beginning of the week are roughly 1/4 the size of the full backup. I know for a fact that the amount of pages/documents that are changes are much, much less than the 1/4 or so that is being backed up. Does the differential contain more data than the actual bits that have changed, or am I missing something as far as what all is changed when a file is updated? Does editing a 50 KB file result in 300 MB worth of changed bits in SQL? Those are approximations, not the actual numbers. However, the first differential being roughly 1/4 the size of the full backup is consistent, and there's also consistency in differential backups seemingly being larger than what's actually been modified. I would've tacked this on to the other question, but Stack Exchange said to avoid asking for help, and only to answer the question. I suppose this isn't necessarily a discussion board (this is my first post). For what it's worth, I've had the same results whether using Powershell or the GUI. Thanks in advance.
Differential backups are data of all the changes since the last full backup. I think you are thinking of incremental backups, which backup since the last backup of any type. So essentially the differential backups being that size are stating that much has changed since the last time you did a full backup.
So in conclusion, the differential backups are copying the same file for every time it's changed, which may be bigger than the original backup because it has more than one file per change.
|show 1 more comment|
While taking the differential backup apart from the modifications after the full backup, it also takes some primary file references, log entries, and a number of other things.
So no much changes in data also give size of differential backup equal to full.