Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have created scheduled backup and shrink maintenance plans for my SQL 2005 instances using the tools in SQL Server Management Studio.

I am wondering, is there an equally easy method for compressing the backups once they have been created?

share|improve this question
add comment

6 Answers

up vote 3 down vote accepted

Assuming that you've been beat down enough about shrinking your database (if not let me know, I'd be happy to add my $.02)...

Native Backup Compression wasn't added until SQL Server 2008. So with SQL 2005 you need to look to a third party tool such as LightSpeed, HyperBac, SQL Backup (Redgate), etc.

If that won't work for you, you can use something like 7zip to compress the files after you write them to disk. Just make it another step in the job.

share|improve this answer
    
& look at gnugpg for encrytion –  Nick Kavadias Oct 9 '09 at 1:44
    
7zip = good. NTFS compression = not good –  squillman Oct 13 '09 at 14:29
add comment

Firstly, what "shrink" plan are you using? As Paul Randal would say, don't shrink anything.

There are third-party tools out there that allow you to compress your backups. Check out SQL Toolbelt from Red-Gate, for an example.

share|improve this answer
    
Regarding Mr Randal, I've had him vote me down for suggesting a shrink operation. He's quite mean about it and I've learnt a great deal from his blog. –  user3914 Oct 8 '09 at 20:05
add comment

Have a look at HyperBac, this is the easiest of the third party products to integrate and use, you can perform native backups in SSMS or Query Analyzer and get automatic, on the fly compression

share|improve this answer
add comment

is there an equally easy method for compressing and encrypting the backups once they have been created?

Alas, not natively no.

I use Red Gate's SQL Backup for that though and like it quite a bit.

share|improve this answer
add comment

You should (almost never) do scheduled shrinks, especially on your data files. Actually, not good for your log files, either.

SQL 2008 has native support for compressed backups, but for SQL 2005, you'll have to use a 3rd-party tool like Quest Lightspeed.

share|improve this answer
add comment

SQLSafe by Idera can do it. They have a freeware version.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.