1

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.

  • What tool was used to encrypt the backup? – Antoine Hernandez Aug 11 '16 at 19:42
  • I don't think backup encryption is there in SQL Server 2008 r2, are you saying that the database has transparent data encryption enabled and you took backup of the database? – Shanky Aug 12 '16 at 11:19
  • Thanks, Shanky, I believe I mistakenly believed that TDE and backup encryption were introduced at the same time because the documentation for TDE states that it can be used to implement encrypted backups. I'll revise my answer today. – Michael Keleher Aug 12 '16 at 13:40
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.