You may notice the below error while configuring your SQL server to support backup compression by executing sp_configure ‘backup compression default’ ‘1’ SQL command.
The configuration option ‘backup compression default’ does not exist, or it may be an advanced option.
The reason for this failure is, simple. You SQL server version is not supporting compression. Per Technet (http://technet.microsoft.com/en-us/library/bb964719.aspx), only SQL 2008 enterprise and later versions supports backup compression. That means, if you have any flavors of SQL server 2008 except enterprise version, you can not use backup compression feature. However, you can still use any version of SQL server 2008 to restore compressed backups. When it comes to SQL server 2008 R2(not that I earlier talked about only SQL 2008 — not R2), we can use any version to backup in compressed format and similarly the restorations.
To find the version of your SQL server, you can either check it from GUI or just use the below powershell code.
$SQLServer = "Myserver1" [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLServer $server | Select Name, Version
This code returns the version number(like 10.0.2531). You can refer http://sqlserverbuilds.blogspot.in/ to know product name of this version.
Please feel free to post if you have any questions.