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?
asked Oct 8, 2009 at 19:57
Add a comment
|
6 Answers
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.
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.
-
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.– user3914Oct 8, 2009 at 20:05
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
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.
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.
SQLSafe by Idera can do it. They have a freeware version.
