SQL Server Performance

Backup Compression

Discussion in 'SQL Server 2008 General DBA Questions' started by CanadaDBA, Apr 28, 2009.

  1. CanadaDBA New Member

    I know there are tools that squeeze SQL backup on the air. And I know SQL 2008 has the backup compression built in. But I am looking for a way to force SQL server 2k5 to produce smaller SQL backups or compressed backups? Is there a coding approach?
  2. MohammedU New Member

    There is not built in functionality for backup compression in SQL 2005 and you have to use third party tools like LiteSpeed etc...
    You can use the sue multiple backup files to reduce the size of the back files but not total backup size.
  3. satya Moderator

    HOw big is the backup file and what kind of compression ratio you are trying to achieve?
  4. CanadaDBA New Member

    I have a 10.5GB BAK file and I have to transfer it to another country every night. I thought to make it smaller. But actually, the reason I posted this is that I want to start a challenging work to write some codes to create smaller BAK files. May be some want to help and we can do this all together. There are tools on the market more efficient and not so expensive but I want to challenge. J
  5. pzhu1968 New Member

    You need to get 3rd party product. I used litespeed from quest. It is pretty good.
  6. satya Moderator

    This is an expensive task so you may need to spend $$$ to achieve the best, still you could try with usual SQL 2008 backup compression feature and ZIP it (outside SQL) to transfer or have you tried it already?
    BTW how good is the network bandwidth between the sites?
  7. GRE New Member

    As mentioned in previuos posts, to compress a SQL Server 2k5 database you will need a third party tool... Regate's SQL Backup tool can also compress your backups. Obviuosly there is a license cost assocaited with this. This tool can also encrypt your backups too if that is something that would insterest you.
  8. deepakontheweb New Member

    have a look on below link, Although its not recommended on production; but in case you don't want to spend money on 3rd party products and also restricted to use sql 2005.http://www.databasejournal.com/features/mssql/article.php/3556461/Compress-SQL-Server-backups-using-WinZip--Part-I.htm
  9. ashish287 New Member

    Hope when you asking about database backup compress then first you checking that before full backup you dont have big log file in size. Most of the sapce you can make by making the comrpessing the log file before full backup. After then if all set from the datababse side then you can use third party tool or SQL Server utility.
    Also as you mtnioned that you have to transfer everynight full backup to remote location, why not consider the Replication or Log shipping option?

Share This Page