Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

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.

share|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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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