question

sunithalee143 avatar image
sunithalee143 asked

Backup tool usage

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.
sql-server-2005backup-compressionthird-party-tools
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AaronBertrand avatar image
AaronBertrand answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@TRAD - sorry if this seems to be splitting hairs but RedGate's backup tool is SQLBackup Pro. HyperBac is a compression technology they have that silently (de)compresses files. The mounting of the backup file as a live database is handled by SQL Virtual Restore - a 3rd product! SQLBackup Pro does encryption, has a compression estimating tool, and has a tool that will convert the bespoke file back to native format if you should need it. Fully working 14 day trial can be used to assess compression gains.
1 Like 1 ·
Ah yes, SQL Backup is a great tool and I use it at my shop. Although Hyperbac isn't a backup agent like SQL Backup it does bill itself as "Save time and space: silently compress SQL Server backups by up to 95% for faster, smaller backups" It appears to be a service that "intercepts read/write requests whenever you take a backup or run a restore". Virtual restore is a product we are looking at purchasing. It has some serious value in being able to mount a backup file as a physical database for those situations where you just need to retrieve a table or small number of records. It saves a lot of time and space.
0 Likes 0 ·
Yeah HyperBac gets in-between the HDD and the OS and compresses/decompresses on the fly. Not seen it stressed but it must take some % of CPU so make sure you have headroom. @Grant Fritchey has done some work on evaluating it. Check his blog, and the RG website for webinars - it may have got there by now too.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.