compressed backup sql 2005

Question

compressed backup sql 2005

Joe-420121

Hall of Fame

Points: 3152
More actions
January 14, 2010 at 6:49 am

#138112

SQL server running out of space for a full backup; Backup size 460GB; is there any tool (idea) that can compress the backup size to a smaller one. Pls help

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Choices are to use a third party tool; Quest's LiteSpeed, RedGate's SQL Backup, Ideara's SQL Safe, HyperBac's HyperBac. Each have their pro and cons. Personally, we use HyperBac where I work. What makes it nice is that we still use MS SQL Servers native backup/restore commands and HyperBac handles the compression/decompression behind the scences.

The other choice is to use a utility such as 7Zip, PKZip, WinZip, WinRAR, etc with a command line interface to compress the sql server backups when they complete. We actually have PKZip here, but we don't have the command line application for the servers, so it was a manual process when we use it to archive our backup files. We also have since downloaded 7Zip (freeware) to provide a command-line compression utility for other purposes.

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 2

We use RedGate, all in all a decent product that gets the job done, also their support is very good.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 3

Being on SSC, I would say use RedGate SQL Backup, which I haven't used yet but, plan to in the future.

Quest Litespeed is one good tool too, and I am happy with its features. I have a 450GB SQL 2000 DB and it takes Litespeed 55 mins to back it up and the compressed backup file size comes to 105 GB.

Thanks..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is my suggestion, if you are going to look into a third party backup tool, get evaluation versions of all of them, test them out, compare them side by side, the select the one that works best for you and mets the price point your company is willing to spend. Some products are licensed by instance, some by server; so pay close attention to that and your environment.

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 5

Joe-420121

Hall of Fame

Points: 3152

January 14, 2010 at 8:40 am

#1103655

Thanks for all your help; I am evaluating RedGate.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Now I'll plug HyperBac. I like HyperBac because it works in the background, no changes are needed to existing processes. We still use the T-SQL native backup/restore commands. Also, if your using SSIS to export files and you configure HyperBac correctly it can compress output files as well.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

Lynn Pettis (1/14/2010)
Now I'll plug HyperBac. I like HyperBac because it works in the background, no changes are needed to existing processes. We still use the T-SQL native backup/restore commands. Also, if your using SSIS to export files and you configure HyperBac correctly it can compress output files as well.

I have used both Litespeed and Hyperbac. For pure compression issues, I really like Hyperbac because you don't have to change anything in SQL Server. You use the native tools for backup and either configure Hyperbac to compress anything going to a folder, or everything with a particular extension.

On the other hand, if your maintenance window is shrinking - using Litespeed dramatically decreased the amount of time the backup (and restore) took. The disadvantage is that you have to use their tool, or build scripts to execute the extended procedures they provide.

Now, knowing how Litespeed (and the other products) get their speed - you can easily do the same thing. Each of the other products uses multiple files (striped backup) to speed up the process. Implement a striped backup to a Hyperbac enabled folder and you get both fast and compressed backups.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 8

Thanks again for the valuable info. it is smiply great useful tools and saved a lot.