25112
asked on
SQL 2012: how to restore from Enterprise edication backup
when a backup from SQL 2012 enterprise is placed in a Standard edition for RESTORE,
Database1)
Database cannot be started in this edition of SQL Server because part or all of object 'SOSPgrm' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database2)
Transparent Data Encryption is not available in the edition of this SQL Server instance. Part or all of object 'tblDDLProgram' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
can you recommend a way to work around this?
is it possible to make a RESTORE in the enterprised edition with different names, then remove the features on those databases and then BACKUP/RESTORE TO standard edition?
Database1)
Database cannot be started in this edition of SQL Server because part or all of object 'SOSPgrm' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database2)
Transparent Data Encryption is not available in the edition of this SQL Server instance. Part or all of object 'tblDDLProgram' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
can you recommend a way to work around this?
is it possible to make a RESTORE in the enterprised edition with different names, then remove the features on those databases and then BACKUP/RESTORE TO standard edition?
ASKER
>> remove the compression and TDE and convert the vardecimal storage format
Are these are at database level or should I find what objects are carrying these features? if so, is there a DMV that can show which objects
Are these are at database level or should I find what objects are carrying these features? if so, is there a DMV that can show which objects
Encryption is at database level and Compression at table level.
To remove TDE:
ALTER DATABASE DBName
SET ENCRYPTION OFF
DROP DATABASE encryption key
For disable the compression it will give you more work since you need to go through all objects that have the compression enabled. You can follow this MSDN article to see how to do it.
ASKER
Let's say the main database is 'A1'. I make a RESTORE from A1's backup and make is A2. Then if I run
ALTER DATABASE DBName
SET ENCRYPTION OFF
DROP DATABASE encryption key
on A2, that won't affect A1's TDE at all,? can you confirm.
ALTER DATABASE DBName
SET ENCRYPTION OFF
DROP DATABASE encryption key
on A2, that won't affect A1's TDE at all,? can you confirm.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks Vitor
ASKER
a followup question: https://www.experts-exchange.com/questions/29028369/remove-database-ref-from-dm-database-encryption-keys-SQL-2012.html
if you have a chance to attend. thanks.
if you have a chance to attend. thanks.
In the restored database, remove the compression and TDE and convert the vardecimal storage format to a supported data type in Standard Edition. You can then backup the convert database and restore it in a Standard Edition.