Enterprise TechSQLHow to Downgrade SQL Database from Enterprise to Standard Edition May 16, 201612629 views0Share By IG ShareIf 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:Table/index partitioningPolybase Head NodeStretch DatabaseRLS and DDM security featuresTransparent data encryption (TDE)Change data capture (CDC)Data compression (Page or Row)Column Store IndexPartioned TablesAlways on Availability GroupsSome 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 availableIf 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. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...