I have a database with full backup of almost 2GB.
When I perform a full backup (without compression) and then compress it with winrar it become 140MB, but when I backup WITH COMPRESSION syntax the result is 315MB, and unfortunately after compression with winrar the size not changes.
How can I use T-SQL for full compression at maximum level?
Asked
Active
Viewed 1,729 times
2
Hossein POURAKBAR
- 117
- 3
2 Answers
6
SQL Server's built-in backup compression only has one level of compression (as Mark and Adrian have already said). The most likely reason that you are getting a smaller final file by compressing the uncompressed backup is that it will contain large amounts of plain text. WinRAR and other traditional compression utilities are very good at compressing plain text.
An alternative means of getting smaller backup files is to use a third-party backup application. There are several of these on the market, including Red Gate's SQL Backup Pro and Idera's SQL Safe Backup. These contain more efficient compression algorithms that should give you smaller backups without post processing. I don't use either of these personally so can't recommend a specific one.
Simon Byles
- 116
- 1
- 4
