question

Bab avatar image
Bab asked

Compression backup files SQL 2008

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
compression
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.

What SQL Edition you have?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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 http://www.red-gate.com/products/dba/sql-backup/compare
2 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.

I am a fan of Red Gate's SQL Backup personally.
0 Likes 0 ·
I would use a proper SQL backup product that supports compression if possible because that will typically be faster than a normal backup (less disk I/O). If you have to compress the file afterwards it will take longer and some compression apps will choke on a big backup file. I did use WinRAR without issues for this purpose for a few months. It has a good command line so it's good for scripting into SQL Agent jobs etc.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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][1] for more info. If not, then you may consider third party backup tools that allow compression. [1]: http://msdn.microsoft.com/en-us/library/ms186865.aspx
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.

Kev, I have db maintenance task that backup the db file on datetime stamp. is it possible to use the compression during the creation of the backup files on datetime stamp value. The script i know compress the specific file. BACKUP DATABASE TEST TO DISK='C:\DBBackups\TESTDB.BAK' WITH COMPRESSION ,INIT How should i change this an create TESTDB on datatime stamp?
0 Likes 0 ·
@Bab, I'm sorry I don't understand what you are asking. Can you rephrase the question?
0 Likes 0 ·
My question: DB maintenace task create backup files in datatime stamp format. for example: TESTDB_2011_10_25_083350_0686523.bak I need to compress these files during the backup process. The values after TESTDB could be different. I hope you understand.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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.
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.

Sadhu avatar image
Sadhu answered
The best way is using third-party software to perform beckups with compression. I would recommend [EMS SQL Backup][1] which has more effective compression algorithm than native one. [1]: http://www.sqlmanager.net/products/mssql/sqlbackup/
2 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.

-1. This feels like spam.
0 Likes 0 ·
Wow. There are a few things I would entrust to a small unknown company's products, but my backups are not one of them.
0 Likes 0 ·
apogoreliy avatar image
apogoreliy answered
Backup compression is set off by default. There ia an opportunity to override the default setting when you are creating a single backup or when you set up a schedule for a series of routine backups. There is an interesting article which shows how to configure compression for backup different kinds of SQL Servers versions 2008 and later (2008 R2, 2012). [ http://sqlbak.com/blog/how-to-configure-backup-compression/][1] Hope it will be useful [1]: http://sqlbak.com/blog/how-to-configure-backup-compression/
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.

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.