Downgrade the SQL edition from Standard to Developer Edition

Expert User Verified
Downgrading from higher edition to lower edition in SQL Server, we have to decommission the higher edition then install the lower edition with same setting as higher like instance name, backup location, system DBs location etc.
SELECT * FROM sys.dm_db_persisted_sku_features

Pre-Requisites:

  1. Take the Full backup of VM
  2. Execute the below query below on each database to know whether the database is utilizing any feature that belongs to enterprise edition only SELECT * FROM sys.dm_db_persisted_sku_features
  3. Run SELECT @@VERSION and make a note of exact version and build number
  4. Take the backup of All databases
  5. Note down the current configuration of the database server. Keep track of the installation folder and note down the exact install location. Also make a note of select @@version, sp_configure.
  6. Stop the SQL services
  7. Browse to the location where your system databases (master, temdb, msdb, modeldb) are kept and copy them to a new location (we will get back to them later on). There is no need to copy the other database files as they won’t be affected during the downgrade process.

Implementation Plan

  1. Navigate to the Control Panel and choose Uninstall a program under the Programs section.
  2. Find Microsoft SQL Server 20XX (64-bit)
  3. Click Uninstall/Change, then click Remove
  4. The SQL Server Installer will launch and list all the SQL Server Instances currently installed on the server. Choose the SQL Server instance you want to downgrade and click Next. Click Select All to remove all SQL Server components from the server for this instance and click Next. Now click Remove to begin the uninstallation operation.
  5. Reboot of the server
  6. Install the developer edition of SQL Server with the same instance name and path as the previous installation.
  7. Stop SQL Server service
  8. Copy system databases (master, temdb, msdb, modeldb) of new installed SQL and place it to new location so that we can revert to this state.
  9. Move all databases files back to their original locations. We also need to replace the system database files with the previous one.
  10. Start SQL Service again
  11. Restore all user databases
  12. Verify that databases are online and healthy again with new Edition

References and Credits

Comments

Leave a Comment