locked
Restore of database that previously had TDE enabled fails on SQL Standard 2012 RRS feed

  • Question

  • I am trying to restore a backup from a server running SQL Server 2012 Enterprise onto one running SQL 2012 Standard.  Both are at version 11.0.5058.0 (X64).

    The database is using TDE in the current environment, but I removed it using "ALTER DATABASE [jira] SET ENCRYPTION OFF", and if I rerun that command I get the following error:

    Msg 33108, Level 16, State 1, Line 16
    Cannot disable database encryption because it is already disabled.
    Msg 5069, Level 16, State 1, Line 16
    ALTER DATABASE statement failed.


    However, "SELECT * FROM .sys.dm_db_persisted_sku_features" returns 

    feature_name feature_id
    TransparentDatabaseEncryption 500

    When I try to restore the database on the 2012 Standard system I get the following error:

    Msg 33117, Level 16, State 2, Line 2
    Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.
    Msg 933, Level 21, State 1, Line 2
    Database 'jira' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

    So apparently remanants of TDE remain in the backup, but I can't find anything about the issue expect a reference to 2008 R2 where issue was supposed to have been fixed.

    Any suggestions would be greatly appreciated.
    Wednesday, January 20, 2016 3:40 PM

Answers

  • Did you verify decryption had completed before taking the backup? The process is asynchronous and can take a while depending on size, server resources, etc... sys.dm_database_encryption_keys is the DMV to look at. Details on the state can be found here: https://msdn.microsoft.com/en-us/library/bb677274.aspx

    No great genius has ever existed without some touch of madness. - Aristotle

    Wednesday, January 20, 2016 7:00 PM
  •  Hi Byron,

    Besides disabling encryption using "ALTER DATABASE [jira] SET ENCRYPTION OFF", please ensure that you also drop database key by executing the following script . Then take backup of your database in SQL  2012 enterprise edition, restore it in SQL Server 2012 Standard edition and check if it is successful.

    USE [jira]
    
    DROP DATABASE ENCRYPTION KEY
    
    GO
    



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Thursday, January 21, 2016 8:34 AM
  • Thanks for all the suggestions.  Because the database was so small and the need to migrate was urgent, I ended up creating a new database and scripting the schema to it, then migrating the data into it.  Then I used a backup of that database to migrate to the new server.  No elegant, but it served the purpose.  If I have time later, I'll try dropping the database encryption key prior to the backup just so I can test it for future reference.
    Thursday, January 21, 2016 1:13 PM

All replies

  • Hi,

    1. Did you disable TDE after or before the backup was taken ? If after then your backup is already encrypted

    2. Given that TDE is not supported by SQL Standard, you can't restore a TDE database into SQL Standard

    Wednesday, January 20, 2016 4:02 PM
  • I created the backup AFTER removing TDE and even restarted the service to see if that would get rid of any remnants of TDE, but that didn't help.
    Wednesday, January 20, 2016 4:06 PM
  • Did you verify decryption had completed before taking the backup? The process is asynchronous and can take a while depending on size, server resources, etc... sys.dm_database_encryption_keys is the DMV to look at. Details on the state can be found here: https://msdn.microsoft.com/en-us/library/bb677274.aspx

    No great genius has ever existed without some touch of madness. - Aristotle

    Wednesday, January 20, 2016 7:00 PM
  •  Hi Byron,

    Besides disabling encryption using "ALTER DATABASE [jira] SET ENCRYPTION OFF", please ensure that you also drop database key by executing the following script . Then take backup of your database in SQL  2012 enterprise edition, restore it in SQL Server 2012 Standard edition and check if it is successful.

    USE [jira]
    
    DROP DATABASE ENCRYPTION KEY
    
    GO
    



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Thursday, January 21, 2016 8:34 AM
  • Thanks for all the suggestions.  Because the database was so small and the need to migrate was urgent, I ended up creating a new database and scripting the schema to it, then migrating the data into it.  Then I used a backup of that database to migrate to the new server.  No elegant, but it served the purpose.  If I have time later, I'll try dropping the database encryption key prior to the backup just so I can test it for future reference.
    Thursday, January 21, 2016 1:13 PM