Backup compression

1

Dear Experts,
Will there be any problems if we use the database backup compression?
Anything related to Restores or data problems?

Thanks

2

Hi,
Not really, the only thing is that it will take more CPU while doing backup, given that current server is pretty powerful, it shouldn't make much different.

Hope this helps

1 Like
3

I think that the increased CPU (to do the compression) is a theoretical rather than practical issue

My backup time dropped by 40% when we started making compressed backups and whatever that benefit was to the server (compared to NON Compressed backups) far outweighed any increase in CPU.

I have 40% reduction in time during backup, 80% less disk utilisation, probably slightly worse tape backup utilisation (I expect that SQL's compression is not as good as the tape backup ... but I haven't made any tests) and (rarely used, but whenever it is for me it is very important :slight_smile:) I have 40% better Restore performance.

You can make Compressed Backups the default (in SQL Settings) so you don't have to change anything in your actual backup routine / code.

Now that Compressed Backups are no longer an Enterprise-only feature I see very few, and very rare, reasons NOT to implement it.

The ONLY reason I can see NOT to set Compressed Backups as the default is IF you use ZIP on absolute maximum compression to then ship the backups to somewhere else. Note that the CPU to do ZIP on Maximum Compression is ABSOLUTELY MASSIVE!! so transmission speed / bandwidth would have to be a very significant criteria.

Here's a thread I asked a similar question in a year ago - I got some very useful feedback :slight_smile: and posted the results of some tests I made:

http://forums.sqlteam.com/t/pros-cons-of-database-backup-compression/2263

1 Like
4

here is good read for the backup compression: http://searchsqlserver.techtarget.com/tip/SQL-Server-2008-backup-compression-pros-and-cons