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.
Note: If you buy something from our links, we might earn a commission. See our disclosure statement.
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:
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.
Comments are closed.