Post reply

differential backup newbie question

  • October 19, 2003 at 11:56 pm

    #151952

    Hello everybody,

    I got a question for you. I was wondering if a differential backup file (with INIT) would ever be larger than a full backup file? I've heard somebody tell me their differential backup file was larger than their full backup file, and that didn't make sense to me.

    Does the differential backup file contain like a "snapshot" of the pages that have been changed, or does it contain like a history of sequential changes like a log file has? I hope you guys understand what I mean.

    Thanks.

  • October 20, 2003 at 1:44 am

    #478744

    A differential backup will only backup extents that have changed since the last full backup.

    Even if just a single byte changes in the extent, the whole extent is backed up by a differential backup. Therefore, if you are doing a lot of very small changes, differential backups can turn out to be a lot larger than you would expect.

    In theory, they should never be bigger than a full backup, but I have seen several posts from people saying that they are. I suspect that they are not doing an INIT, and therefore the backup file is growing larger with each diff backup.

  • October 20, 2003 at 6:24 am

    #478762

    There are many cases where a differential backup could be larger than the previous full backup:

    • Many rows have been added. If your database consisted of a few tables with a dozen rows in each when you did you full backup, and then you add several thousand rows to each table, a subsequent differential backup will be much larger than the last full backup.
    • You update variable length columns with longer entries. If you have a varchar(1000) column in a large table that has short values in most rows when you do the full backup, and then perform an update to increase the length of those values, a subsequent differential backup could also be larger; BLOBs could make this even more extreme.
    • You add objects between the full and differential backups. New indexes, tables, etc. could easily cause the differential backup to be larger, particularly during database design.

    --Jonathan



    --Jonathan

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply