2

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!

| improve this question | | | | |
5

Why would a differential backup compressed be almost the size of the mdf?

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.

| improve this answer | | | | |
  • Thanks for the link. I ran it and the SP database has changed 27%. – rsteckly Dec 15 '11 at 23:42
  • 1
    Sharepoint DBs are full of LOB data so I'd expect this – gbn Dec 16 '11 at 6:17
0

share point databases never compress much more , as these dbs are not type of normal transaction database

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.