November 4, 2004 at 2:06 pm
all,
i would like to pose a few questions. what do you believe are the benefits and pitfulls of compressing (zipping) sql backup files? i'm particularly interested in the following and anyones experience:
- what zipped file compression ratios can you expect for different sized databases (e.g. <4GB and >4GB)?
- zipped file data consistency (anyone ever suffered consistency issues?) vs the relatively cheap cost of extra storage nowadays?
- performance impact and issues when compressing large database files on the sql server?
- anything else you can add!
thanks,
lloyd
November 4, 2004 at 2:30 pm
If you have need for compresses backups I would look at SQL LiteSpeed or Idera's SQL Safe both do real time compression of the backup file and reduce the amount of time the backup usually takes. Or if you are on a tight budget Yohz MiniSQLBackup should handle your needs as well. Most Databases compress down 50 to 85% or more. Just zipping up the files will add time and complexity to your backups and restores. Also zip can't handle large files well or at all in some cases. You would have to use rar or another compression method for files over 4 gig I do belive.
Wes
November 4, 2004 at 11:34 pm
See here for a discussion on compression using standard compression tools (WinZip, WinRar etc) vs online backup/restore compression/decompression tools like MiniSQLBackup.
If you are comparing between the two, then online tools wins hands down, in terms of time and processing power. Standard compression tools can probably generate smaller backups, since online tools try to minimize the CPU cycles used during the compression process.
Comparing between using native backups vs 3rd-party backup tools, 3rd party tools will usually gain you the following:
at the cost of the following
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
November 5, 2004 at 11:50 am
I agree with Peter that most 3rd party tools are faster and offer extra features. Also most of them create smaller backups than zipping a "standard" backup manually, though this depends on the tool you're using.
I don't (fully) agree on his comment that these tools have a higher CPU usage. First of all some tools offer the option to restrict the CPU usage and second I know from past experiences that tools like winzip or powerarchiver use even more cputhan most of the 3rd party tools.
One aspect you should consider as well is the fact that some (older) compression tools can't handle files bigger than 2 GB.
M
[font="Verdana"]Markus Bohse[/font]
November 5, 2004 at 11:51 pm
Markus, the point I raised on CPU usage was in comparing between native backups and 3rd party SQL Server backup tools. The native backup imposes almost no extra load on CPU usage, while the 3rd party backup tools will inevitably impose a higher load, due to the compression process. However, the higher load is offset by the reduction in backup time, so if you compare the total CPU load over the duration of the backup time, the effect might just about be equal.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply