I have a database backup which is encrypted by using MS SQL SERVER 2008 R2 Enterprise Version. I now need to decrypt the data. My company cannot afford license for MS SQL Server Enterprise Edition as it is costly. Can we decrypt database backup on MS SQL 2008 R2 Standard version? Or any other cost effective solution is greatly appreciated.
Since the only way to encrypt backups natively in SQL Server 2008 R2 is to use Transparent Data Encryption. You will need to back up the TDE certificate and private key used for TDE in the master database and restore it in the master database of either an Enterprise Edition of SQL Server which is equal to or higher version number or an instance of Developer Edition of equal or higher version. A free option for you is to install SQL Server 2016 Developer Edition and restore to that.
If you used third party software, you will need to refer to the documentation for that product.
If this were SQL Server 2012 or above, then what matters more is the version, which will be in the format 12.0.4422.0, not the edition and also if you have used Enterprise only features in your database. Use the SQL statement below to check:
SELECT * FROM sys.dm_db_persisted_sku_features;
If the above SQL returns any rows, then you can't restore on Standard, but there are other options. You can backup databases on Enterprise and restore them to Developer Edition, which supports all Enterprise features, if you want, but you can't restore a backup from a higher version and restore it to a lower version. If the database is less than 10GB, then you can also restore it to an instance of SQL Server Express, which is free and will support restoring encrypted backups but will not support taking encrypting backups. You will also need to backup the certificate and private key from the master database on your Enterprise system and restore it to your target system assuming you are not using an Extensible Key Management system.