Post reply

SQL Server differential backup file size smaller than previous one?

  • June 11, 2013 at 4:03 am

    #278406

    Hi All,

    I use Production Database Backup Strategy as follows:

    - Full Backup: every 2 weeks

    - Differential Backup: every day

    - Transactional Backup: every hour

    Sometimes differential backup file size become smaller than previous one. Could anyone give me some more detail regarding this issue.

    Is it normal or differential backup file size should increase until the next full backup?

    Is this differential backup reliable? As the database size is very large (around 2.5 TB) and according to hardware resource limitation, It's not possible to restore database to verify backup accuracy.

    Thanks in advance,

    Sanaz.

  • June 11, 2013 at 4:18 am

    #1622797

    Someone took an ad-hoc full backup?

    Lots of data was deleted?

    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 11, 2013 at 4:25 am

    #1622801

    GilaMonster (6/11/2013)

    Someone took an ad-hoc full backup?

    You can check this by restoring the header of the backups and look at (the sequence of) the LSN numbers.

    Do you use compression on the backup? That could explain some differences in the size of the files.

    sani (6/11/2013)

    As the database size is very large (around 2.5 TB) and according to hardware resource limitation, It's not possible to restore database to verify backup accuracy.

    You should get some place where you can verify your backup by using a complete restore. Without a proper restore you will never know for sure if your backup is valid.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • June 11, 2013 at 4:28 am

    #1622802

    HanShi (6/11/2013)

    GilaMonster (6/11/2013)

    Someone took an ad-hoc full backup?

    You can check this by restoring the header of the backups and look at (the sequence of) the LSN numbers.

    Or, much easier, query the msdb backup tables and see if there's an other full backup listed.

    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 11, 2013 at 4:31 am

    #1622804

    Thanks for the reply.

    As a DBA, I have fully control on Backup activities and I am sure no full backup job has been run.

    As a business process point of view, It is possible large amount of data have been deleted. Now I want to know does the deletion consequent to have smaller differential backup size?

    Many Thanks,

    Sanaz.

  • June 11, 2013 at 4:41 am

    #1622807

    sani (6/11/2013)

    Now I want to know does the deletion consequent to have smaller differential backup size?

    It can do, depending on what's deleted and how much. Backups only back up allocated extents.

    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 11, 2013 at 4:52 am

    #1622810

    What do you mean by "what's deleted".

    Could you please give me more detail information or any MSDN link regarding this issue.

    Thanks in advance,

    Sanaz.

Viewing 7 posts - 1 through 6 (of 6 total)

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