Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2001
    Posts
    40

    Unanswered: Question about DIFFERENTIAL backups

    Our production backup schedule is, FULL backup once a month and a DIFFERENTIAL backup every day.

    We are starting the FULL and DIFFERENTIAL backups using something similar to

    FULL Backup
    backup database @DB to disk = @BackupFile

    DIFFERENTIAL backup
    backup database @DB to disk = @BackupFile WITH DIFFERENTIAL

    A full backup was done on 10/6 and the DB size was 48GB. Since then we have been doing DIFFERENTIAL backups. I recently looked at the DIFFERENTIAL backup directory and found something I think is interesting.

    10/07/2005 04:00 AM 8,604,160 prodDB_Diff_200510070400.BAK
    10/08/2005 04:00 AM 1,144,320 prodDB_Diff_200510080400.BAK
    10/09/2005 04:00 AM 1,134,080 prodDB_Diff_200510090400.BAK
    10/10/2005 04:00 AM 21,185,024 prodDB_Diff_200510100400.BAK
    10/11/2005 04:00 AM 7,119,360 prodDB_Diff_200510110400.BAK
    10/12/2005 04:00 AM 163,669,504 prodDB_Diff_200510120400.BAK
    10/13/2005 04:00 AM 14,743,040 prodDB_Diff_200510130400.BAK
    10/14/2005 04:00 AM 120,875,520 prodDB_Diff_200510140400.BAK
    10/15/2005 04:00 AM 1,216,000 prodDB_Diff_200510150400.BAK
    10/16/2005 04:00 AM 5,139,968 prodDB_Diff_200510160400.BAK
    10/17/2005 04:00 AM 4,277,760 prodDB_Diff_200510170400.BAK
    10/18/2005 04:00 AM 2,778,624 prodDB_Diff_200510180400.BAK
    10/19/2005 04:01 AM 750,575,104 prodDB_Diff_200510190400.BAK

    My understanding of DIFFERENTIAL backups from books online is
    "Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"

    MY Question:
    Assuming I am understanding this correctly, all DIFFERENTIAL backups are independent of one another. If a failure happens, all I have to do is restore the latest FULL backup and then
    restore the latest DIFFERENTIAL backup.

    So, I would think the sizes of the backup files should be in ASCENDING order. Why is the size of the backup file from 10/15/2005 (1,216,000 bytes) smaller than the backup file from 10/14/2005 (120,875,520 bytes).

    Assuming a crash happens on 10/15 5AM, I would restore the full backup from 10/6 and then restore the DIFF. backup from 10/15 4AM (Which will apply approx.. 1,216,000 bytes of data). So where has the data in the backup file from 10/14/2005 gone (120,875,520 bytes)?

    Thanks

  2. #2
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    Нou mistaken
    Look for keyword "Differential Database Backups"

    "A differential database backup records only the data that has changed since the last database backup."
    Draw attention - "last database backup". No full backup.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    From BOL:
    DIFFERENTIAL

    Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup. Use this option so that all individual log backups since the last full backup do not need to be applied. For more information, see Differential Database Backups and File Differential Backups.



    Note During a full database or differential backup, SQL Server backs up enough of the transaction log to produce a consistent database when the database is restored.
    Are you doing a lot of updates during the time it takes to do the backups?

  4. #4
    Join Date
    Dec 2001
    Posts
    40
    Thanks so much for your response..

    Right, your quote from BOL is exactly what I read. It has FULL backup written all over it.

    Are you sure it is
    ""A differential database backup records only the data that has changed since the last database backup."

    rather than what BOL says it is

    "Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"

    I wouldn't say lots of updates but the DB is being used.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You have the right idea about restoring the database in your situation. Just 2 restores, and one recovery, no matter what day you go down.

    I am also puzzled by the wild size differences in there. This isn't something strange like the destination of a log-shipping instance, is it? I could see full backups on the source database causing this behavior.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •