Is there any possibility to compress the database backup's to save the storage space on backup media ,if so upto how much extent we can compress?.Presently im working with SQl-2K5.Thanks in advance.
asked
Compressing database backup's
Or... schedule a batch file after the backups already done...
do_compress_bak_files.cmd
@ECHO Compressing BAK files! for %%a in (*.bak) DO ( "C:\Program Files\7-Zip\7z.exe" a -y %%a.7z %%a if errorlevel 1 goto error del %%a :Error move *.7z .\Compressed )
req: 7Zip (www.7-zip.org); cmd.exe; new folder in the backup folder named 'Compressed'
SQL Server 2008 has this ability natively, but in 2005 you will need a 3rd party tool. I personally am quite partial to Red Gate SQL Backup , but there are also competing products. [Edit, forgot the answer the percent question] As always the answer to how much it can be compressed is, it depends on things such as the data structure and the compression setting you use, but to give you a rough idea, I routinely got around 80% compression with SQL Backup.
You could use a non-integrated solution by compressing it after the fact with something like 7-Zip or WinZip though this is likely to give you poor performance compared to the integrated solutions like Red Gate SQL Backup.
We've been nearing 90% with RG Backup but, it depends on your data. We need to move 100s of GB dBs across a slow WAN for the Test environment. We were doing the WinZip thing but it is no where near as quick or convenient. And not having to do the extra steps with the WinZip solution makes life sooo much easier...
I've gotten around 90% and even slightly higher many times, but normally it was just above 80%. It depends on data and also on your settings. Choosing max compression can squeeze several more percent from your file, but it comes at the price of time, especially on slower hardware.
I'm a huge fan of HyperBac. Not only do you get compression of backups, but you can browse the backups to pull individual tables or rows from the backup directly, without running a restore.
Blimey, HyperBac Online looks amazing. How the hell do they do that?!
Pretty cool isn't it. I spoke with their techs like two years ago and heard how they did it, but I've forgotten everything since then.
can you please brief in which formats we can compress the
backups.is there any strategy? Thanks in advance..
im not sure,i'l provide you info ASAP if there exists any tools. As part of disk space management we are asked to share ideas on backups compression.We have very huge production environment making transactions around half million records a day in database.
It can compress full, log, differential... Strategy is to compress everything. Katie, yes, I have seen a 3rd party software corrupt a backup. But then I've seen native backups get corrupted too. The worst problem is when you need to restore 3rd party backups you frequently will need to uncompress them in order to restore them if the software is not available or installed.
Just an FYI, the Hyperbac guys originally developed Litespeed and the moved on to their own company.
I would recommend [EMS SQL Backup][1] which also has very effective compression algorithm. [1]:
http://www.sqlmanager.net/products/mssql/sqlbackup/
1 Person is following this question.
