Hi all, i am having the jobs in SQL Server 2005 for backup.for one database through the job the backup file size is 47 GB.i am not using any backup tool.if i use the HIBACk backup tool then how much size i can compress as i didnt used this tool i dont have idea can any one suggest approximate or exact value for this
tool.as the company is going to use this tool for backup.
The only way to know exactly how much compression you're going to get is to actually run a backup using the tool. Compression relies largely on your data types and the nature of your data, so there's no way for any of us to guess based solely on original size, even for those that have used that particular tool.
Just to pile on to what @aaronbertrand and @ThomasRushton have stated the only way to know the compression rate is to test it. Most tools offer a demo version you can download and try out. I too have some databases where the rate of compression is extreme such as 90%+ whereas other databases I am lucky to see 20%. The only thing I would like to add is that although you may get fast compressed backups, also test the restore? Does the version of the tool you are using going to impact the speed of your restore? Do you have to have the tool installed in order to be able to restore the database to another server? Is everyone dead set on using HIBACK? Have you also evaluated HYPERBACK from Redgate? Not only does this tool compress the backups it can also encrypt them. You can then use virtual restore to mount the backup file as a database for quick access to your data without having to restore the entire database.
To echo what @AaronBertrand says, we have one database that compresses by over 95% on backup, and another that barely compresses by 20%. It's all down to the data and how compressible it is. And the actual tool you use won't have much impact on the compression ratio you get - we did some testing of three different backup products all set to give it their best shot in terms of compression, and they ended up much of a muchness in size and time taken to compress and encrypt a 100GB database.