My client has requested to compress the database backup files but their server does not allow the compression. The compression is turned OFF on their server. How should i compress the backup files without asking them to turn the option ON? or without making any changes on their server. Any suggestion appreciated. Thanks
Unless you have a version of SQL Server that supports compression you are left with 3rd party solutions as your only option. 1 - you could simply zip every backup file with an external process 2 - you could buy a 3rd party backup solution that does that backup and compress in one go. many vendors do a free trial that you could install and test. RedGate do one here
If you are using SQL Server 2008 then backup compression is only available in Enterprise edition. From SQL Server 2008 R2, it was then available from Standard edition. If you are on a version/edition that supports it, and the server default is OFF, then you can specify `COMPRESSION` in the `WITH` options of `BACKUP DATABASE` and `BACKUP LOG` commands. See [MSDN] for more info. If not, then you may consider third party backup tools that allow compression. :
One thing to bear in mind is the differences in the way that the compression options work in terms of disk space. As I understand it, * Using a post-backup compression utility will require the most disk space to operate * SQL Server in-built compression still requires enough disk space to do a full uncompressed backup, as it creates a backup device big enough to store an uncompressed backup before shrinking it down when the backup is complete * third party utilities require least space, as they write directly out to a compressed file. edit -- Seems like I'm wrong about the second point. states that a compressed backup file is initially created at 1/3rd the size of the database being backed up, and then dynamically grown if required, or truncated at the end of the backup operation. There's a TraceFlag (3042) that'll change this behaviour. My apologies for providing misleading information here.