locked
SQL Server 2008: Compression Backup Vs Non-Compression Backup RRS feed

  • Question

  • I am not sure the difference the between SQL Server 2008: Compression Backup Vs Non-Compression Backup.

    I tried it and found that SQL Server Compression Backup is faster to compress and 1/4th of the size as well. Not sure then what is use of Non-Compression Backup. Is it just a backward compatibility or is there any genuine usage.

     

    Monday, April 18, 2011 8:55 AM

Answers

  • As you have found the backups are smaller, and can take less time to complete. But the flip side of compression is the increased cpu load which you have to consider for your environment. That said if you are able to use it on your version/edition of sql then in most cases it is a good tool to have.

    Backup Compression (SQL Server)

    http://technet.microsoft.com/en-us/library/bb964719.aspx


    Jon
    Monday, April 18, 2011 9:17 AM
  • Compression is very CPU intensive and some time you don't want the backup to take all the CPU resources from the queries. the compressed  backup is also not able to be restored into an SQL 2005 database.

    Normally, it is a good idea to use the compression as it produces much smaller file size and reduce the backup/restore time too. As a suggestion, you can use the resource governor to limit the CPU usage by the backup job to reduce the CPU resource contetions.


    Monday, April 18, 2011 11:14 AM

All replies

  • As you have found the backups are smaller, and can take less time to complete. But the flip side of compression is the increased cpu load which you have to consider for your environment. That said if you are able to use it on your version/edition of sql then in most cases it is a good tool to have.

    Backup Compression (SQL Server)

    http://technet.microsoft.com/en-us/library/bb964719.aspx


    Jon
    Monday, April 18, 2011 9:17 AM
  • Compression is very CPU intensive and some time you don't want the backup to take all the CPU resources from the queries. the compressed  backup is also not able to be restored into an SQL 2005 database.

    Normally, it is a good idea to use the compression as it produces much smaller file size and reduce the backup/restore time too. As a suggestion, you can use the resource governor to limit the CPU usage by the backup job to reduce the CPU resource contetions.


    Monday, April 18, 2011 11:14 AM
  • You can only use Compressed Backup on Enterprise version of SQL Server. If you're using this version, then sure, use Compressed Backup.

    Denis Gobo has a blog post in regards to performance gains:

    Testing backup compression in SQL Server 2008

    and also

    Do You Get A Benefit From Compressing Backups If You Already Have Compressed Data?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, April 18, 2011 5:19 PM