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.

    --

    David

  • 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.

    Thanks,

  • 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.

  • My guess is the SAN replication has VSS support in Windows that is triggering the SQL Server VSS Writer at backup time. The VSS Writer is effectively creating a snapshot on the database and running a full backup – which you probably see logged in msdb with a GUID as the destination.

    You have a few options:

    1. Disable the SQL Server VSS Writer service. That should prevent interference with SQL Server, but it would not allow for stable copy operations of the database files, meaning that transactions in flight may cause transactional integrity issues on the SAN copy. If you don't care about the SAN snapshots, then this may be a good option
    2. Check the SAN snapshot software and see if there is SQL Server integration and an option to create COPY ONLY full backups. A copy only full backup would leave the differential base unaffected and allow you to restore your differential backups
    3. Stop using differential backups. If you use full backups only (despite the degraded backup speed and increase in storage), you’ll find the full backups from LiteSpeed and the full backup from the SAN will not interfere with one another
  • Thanks a lot David ! This was very helpful.