question

venkatreddy avatar image
venkatreddy asked

Compressing database backup's

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.

t-sqlperformancebackup
10 |1200 characters needed characters left characters exceeded

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

kefealo avatar image
kefealo answered

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'

10 |1200 characters needed characters left characters exceeded

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

TimothyAWiseman avatar image
TimothyAWiseman answered

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.

3 comments
10 |1200 characters needed characters left characters exceeded

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

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...
2 Likes 2 ·
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.
0 Likes 0 ·
Thanks a lot Timothy..
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

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.

7 comments
10 |1200 characters needed characters left characters exceeded

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

Blimey, HyperBac Online looks amazing. How the hell do they do that?!
0 Likes 0 ·
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.
0 Likes 0 ·
can you please brief in which formats we can compress the backups.is there any strategy? Thanks in advance..
0 Likes 0 ·
this was great information to share... But i wonder.. what are the chances of the backup files getting corrupt using a third party tool.. has anybody encountered any such situation?
0 Likes 0 ·
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.
0 Likes 0 ·
Show more comments
Sadhu avatar image
Sadhu answered
I would recommend [EMS SQL Backup][1] which also has very effective compression algorithm. [1]: http://www.sqlmanager.net/products/mssql/sqlbackup/
1 comment
10 |1200 characters needed characters left characters exceeded

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

-1 for spammy 4-years-later answer.
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.