I'm trying to backup my organization's production databases on SQL Server 2008 R2 Standard. I'm using a T-SQL script to backup with DIFFERENTIAL and COMPRESSION selected.
I'm astounded at the size of the backups relative to the mdf file sizes. For example, we have one sharepoint database that is 307 mbs in mdf. It's backup–with differential and compression selected–is 280 mbs. How is this compressed or differential?
I've tried backing up using .7z and zip and it doesn't really achieve any more compression.
Am I missing something? Why would a differential backup compressed be almost the size of the mdf?
Thanks!
Best Answer
A differential backup contains all extents that have changed since the last FULL backup. Could be that a large percentage of the database has changed since the previous FULL, could be that an index maintenance task has rebuilt/re-organised tables.
Try Paul Randal's How much of the database has changed since the last full backup? script to verify what percentage of the database extents have changed.
If zip compressing a backup has as little effect as using SQL backup compression, good odds a large percentage of the space is taken up with LOB data that can't be compressed further e.g. PDF or image types.