How to Downgrade SQL Database from Enterprise to Standard Edition

If you want to Restore a SQL Server 2014/2016 backup from a higher version of SQL to a lower version, i.e. from Enterprise to Standard Edition, you can do so, but before you do that, you need to ensure that you are not using any of the Enterprise Edition features enabled on the source system.

Before going ahead with the backup process, you need to ensure that none of the Enterprise Edition features are being used. Use the following SQL Query to find that out:

SELECT * FROM sys.dm_db_persisted_sku_features;

Here are some of the features that might disallow you to directly downgrade to a Standard Edition of SQL Server:

• Table/index partitioning
• Polybase Head Node
• Stretch Database
• RLS and DDM security features
• Transparent data encryption (TDE)
• Change data capture (CDC)
• Data compression (Page or Row)
• Column Store Index
• Partioned Tables
• Always on Availability Groups

Some features like Backup compression and Encrypted backups are supported on both SQL Server Standard and Enterprise Edition so there should not be any problems.

Sample Error Message:

Processed 160 pages for database 'EnterpriseOnly', file 'EnterpriseOnly' on file 1.
Processed 5 pages for database 'EnterpriseOnly', file 'EnterpriseOnly_log' on file 1.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'EnterpriseOnly'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 1
Database 'EnterpriseOnly' cannot be started in this edition of SQL Server because part or all of object 'compressed' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 1
Database 'EnterpriseOnly' cannot be started because some of the database functionality is not available

If there are features like Partitioning enabled which is blocking the backup restore job, use a script to remove it and recreate the index’s. See example.

Disclaimer: The Questions and Answers provided on https://gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.