Menu
I'm trying to backup a database in SQL Server 2008 and have the database compressed using the new compression feature. However, when I run the following code, I get a weird error message:
Backup Database <Database> To Disk 'C:\Backup' With Compression
I get this error message:
Backup Database With Compression is not supported on Standard Edition
Does this mean I have to upgrade to the full version or is there a way to enable compression in standard edition?
687
Backup compression is supported on SQL Server editions: Enterprise, Standard, and Developer. Every edition of SQL Server 2008 and later can restore a compressed backup.
Nope. SQL Server 2012 doesn't support compressed backups in Web Edition (for example).
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Click the Database settings node. Under Backup and restore, Compress backup shows the current setting of the backup compression default option.
Your data is probable somewhere inbetween, so it'll compress somewhere between 0.1% and 99.9%. The backup industry likes to advertise that "most" data can be 50% compressed. I've found this to be optimistic, but not terribly far off. I've found our MSSQL DBs generally compress less than 30% however.
I realize this is an old post, but just to throw this out there since it showed as one of the first few entries on a Google search - with SQL Server 2008 R2, Standard Edition also gets backup compression. More information here: http://technet.microsoft.com/en-us/library/bb964719.aspx
143
Backup Compression is not supported on SQL 2008 Express, Workgroup, or Standard editions, only on Enterprise. There is no way to turn this on without upgrading your version to Enterprise.
Although you can't backup with compression on those versions, you can RESTORE from compressed backups.
See http://technet.microsoft.com/en-us/library/bb964719.aspx for more info.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
