Post reply

Irregular Size of Differential Backup File

  • June 29, 2009 at 2:58 am

    #206522

    Hi Friends,

    During Backup of the Different databases I got a problem, I tried a lot to solve it but before that i am having some queries.Help me in this aspect.

    1)There is Differential Backup on the 20 Databases daily and Weekely Backup for once a week. On 26 I observed that two databases having backup file size as 200GB,180Gb,240Gb, I just move those files to another drive so as to free up the disk space. But again on 27th and 29th the Differential backup of the file was created and the size was again in the order of 230GB:( . And also the weekely backup for those Databases was also gets failed on 28th(Weekely Backup Date).What will be the reason for this type of growth of size of differential backup files?

    2)Is the differential backup depends on the full backup file?

    3)Suppose the full backup of the DB is not existed (or) moved to another location - what will happen to differential backup file size?

    4)How to over come these problems?

    Thanks in advance,

    Venu Gopal.K
    Software Engineer
    INDIA

  • June 29, 2009 at 4:47 am

    #1016704

    venu_ksheerasagaram (6/29/2009)

    2)Is the differential backup depends on the full backup file?

    No, but you need both to restore a database

    3)Suppose the full backup of the DB is not existed (or) moved to another location - what will happen to differential backup file size?

    Nothing.

    The size of the diff backup depends only on the amount of data changed since the last full backup. SQL doesn't need to read the full backup file to know that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 29, 2009 at 5:55 am

    #1016748

    Thanks for u r answers.

    1)Let me know onw more thing. Why the differential Backup getting too large(in order of 230GB)?

    2)Is the Full Backup required for the very first time of the diff backup (so that to decrease size of diff backup)?

    3)With out Full Backup of a DB --Is Full Backup size == Diff Backup size?

    4)Is it necessary that the full backup file and diff backup file has to locate on the same machine?

    Venu Gopal.K
    Software Engineer
    INDIA

  • June 29, 2009 at 6:05 am

    #1016757

    If you've never taken a full backup, you cannot take a diff backup. It will fail.

    The size of a diff backup depends on the amount of data that's changed since the last full backup. Diffs are always based on the previous full backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 29, 2009 at 6:12 am

    #1016764

    Venu,

    If you don't take full backup for a long time, then the size of full backup will increase and become as big as full backup itself.

    Since differential backup will only backup pages that changed from last full backup, if after some time all pages are changed, then it needs to backup the entire database.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 29, 2009 at 6:24 am

    #1016770

    Hi JeoRoshan,

    Thanks for u r help. Let me know one more thing? I am having one case here-- I have given a DB in which there is no FULL Backup on the system. But as i run Diff Backup it gets succeeded for two days, but for the two days the size of the diff backup file is like 240Gb,230Gb. Is this unpredictable size of diff file is due to the absence of FULL backup?

    Thanks,

    Venu Gopal.K
    Software Engineer
    INDIA

  • June 29, 2009 at 6:30 am

    #1016776

    venu_ksheerasagaram (6/29/2009)

    I am having one case here-- I have given a DB in which there is no FULL Backup on the system.

    There was a full backup done sometime in the past. If there wasn't the diff would fail. A differential backup has to be based off a previous full backup.

    You are aware that you need the full backup to restore the diff? If you don't know where the full backup file is, those diff backups are completely useless as they cannot be restored

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 29, 2009 at 6:44 am

    #1016789

    Venu,

    As Gail Shaw said, you should include full backup in your backup plan.

    In case of disaster you need to restore latest full backup (with no recovery option), then restore your latest differential backup over it.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 29, 2009 at 8:13 am

    #1016850

    i've had this on a few databases for years. first noticed it around 2 years ago on a 300GB databases where some differential backups were 150GB or more. on other 100GB databases the diff backup was only a few GB. Even opened up a case with Sun Micro because I thought it was a Netbackup issue.

    It happens because SQL will backup an entire page if there is a change on it, not just the change. On some of our databases where developers control too much of how the schema is set up we see this. usually because of a lack of PK's and/or clustered index. simple changes affect most of the pages, so a lot of redundant data is backed up

  • June 29, 2009 at 8:22 am

    #1016855

    SQL Noob (6/29/2009)

    It happens because SQL will backup an entire page if there is a change on it, not just the change.

    Actually SQL will backup an extent (8 pages) if there's a change on any one of those 8 pages. SQL always works with pages or extents when it comes to the storage engine, never smaller pieces.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 29, 2009 at 9:59 pm

    #1017353

    Ok , Now i got an idea regarding how the differential Backup Works.

    But let me know What are the possibilities to overcome the reducing of the Differential Backup Size and What is the correct way of Database configuaration at the time of creation and the setting up of a Differential Backup Maintenence Plan.

    Thanks in Advance,

    Venu Gopal.K
    Software Engineer
    INDIA

  • June 29, 2009 at 10:01 pm

    #1017355

    Ok , Now i got an idea regarding how the differential Backup Works.

    But let me know What are the possibilities to overcome the reducing of the Differential Backup Size and What is the correct way of Database configuaration at the time of creation and the setting up of a Differential Backup Maintenence Plan.

    Thanks in Advance,

    Venu Gopal.K
    Software Engineer
    INDIA

  • June 29, 2009 at 10:03 pm

    #1017356

    Ok , Now i got an idea regarding how the differential Backup Works.

    But let me know What are the possibilities to overcome the reducing of the Differential Backup Size and What is the correct way of Database configuaration at the time of creation and the setting up of a Differential Backup Maintenence Plan.

    Thanks in Advance,

    Venu Gopal.K
    Software Engineer
    INDIA

Viewing 13 posts - 1 through 12 (of 12 total)

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