Difference in differential backup sizes

We are observing a weird behavior in the differential backup sizes. The backup of a database which was taken on Thursday was of size 25 GB and on Friday it's just 2 GB and today it's 19 GB. What could be the reason behind it? We can't use these backups for recovery as they are failing throwing the error the differential backup cannot be used for restore because the database has not been restored to the correct earlier state.

  • The only thing that would normally cause a reduction in size of a differential backup is if a new full backup were run between the differentials. In your case I would check to see if someone ran a full backup between the 25GB differential and the 2GB one. That full would not have to be a LiteSpeed one. Someone may have run a native full backup. You would need to check the msdb tables to be sure. There is a table called backupset that contains the backups.

    In order to restore a differential backup you first need to restore it's related full backup.



  • Thanks David.

    We have SAN replication going on at the backend which messed up the differential backups. But my question here is- Is there any way we can incorporate both SAN replication and Normal sql backups to work simultaneously. We need to find out a solution which accomodates both. Any suggestions would be helpful.


  • Why do you say the SAN replication messed up your differential backup? 

    You mean it corrupted a file that was your differential backup?  Or it caused SQL to backup incorrectly when doing differentials?  A corrupted file I could see, but causing SQL to backup the wrong pages I don't.

    SAN replication and SQL backups should work simultaneously and not even know the other exists. 

  • I think i misleaded you by quoting wrong words. Sorry for that.

    Backups are not corrupted. It caused SQL to backup incorrectly when doing differentials. For some reason in our environment SAN replication is causing issue as SQL recognizes and stores info of SAN replication in msdb system tables. I don't have enough knowledge on how SAN replication works. But at the backend it's running as a Microsoft sql server vss writer application and in system tables the physical device name is getting stored something similar to the below --{DEB921EC-C814-43F1-BB9F-930172B06166}3

  • That's correct. Many SAN snapshot capabilities leverage VSS for transactionally consistent backups. Running an "out-of-band" full backup from the SAN changes the differential base, meaning that future differential backups are now based off the SAN snapshot / full backup and not the last VDI-based backup performed on SQL Server natively, or with a product like LiteSpeed.