At some point in your career working with SQL Server, you will run into a situation where the wrong edition of SQL Server has been installed on a server and will need to change the edition for licensing reasons. Whether it is Enterprise Edition where Standard Edition should have been installed, Enterprise Edition where Developer Edition should have been used, or my favorite, Evaluation Edition where the 180 day trial has expired and Enterprise Edition isn’t going to be used, the only route available for downgrading the edition is to uninstall and reinstall SQL Server entirely. SQL Server Setup makes upgrading editions a piece of cake with SKUUPGRADE as a command line option for going from Standard/Developer/Evaluation to Enterprise, but anything else requires a full uninstall and reinstall to change the SKU/Edition and then restore all of the system and user databases to the new instance, which typically means a lot of work. I hate having to restore system databases and avoid having to do it if possible, so here is how I do this process and minimize the work required:
No matter what you are going to have to do an uninstall and reinstall of the SQL Server instance to downgrade the SKU. However, you can save yourself some time and the headache of trying to restore the system databases if you are careful about what you do. I have done a plenty of SKU downgrades in the past and the easiest way to do it, and I am not saying this is the Microsoft supported way but that it works if done correctly, is to:
- Take a good backup of all of your databases (system and user).
- Run SELECT @@VERSION and note the specific build number of SQL Server that you are currently on.
- Shut down your existing instance of SQL Server.
- Copy the master, model, and msdb database files (both mdf and ldf), don’t move them copy them, from the current location to a new folder that you mark as readonly.
- Uninstall SQL Server from the system.
- Reboot the server.
- Install SQL Server Standard Edition.
- Apply the necessary Service Pack and/or Cumulative Updates to bring the instance up to your previous build number.
- Shutdown SQL Server.
- Copy the master, model, and msdb database files (both mdf and ldf) from the folder you saved them in to the correct location for the new install and remove the readonly flag from the files, and change the file ACL’s so that the SQL Service account has Full Control over the files.
- Startup SQL Server and if you did it correctly it will startup and be exactly where you were before you made any changes, with all of your user databases online and you should be ready to let applications connect and resume operations.
If you screw something up in the above, you still have your backups and you can run setup to rebuild the system databases and then go about following the Microsoft supported path for restoring the system databases and then user databases into the system to bring it online. Essentially the file copy is no different that what would occur through attach/detach you are just doing it with system databases which is not explicitly supported, but it does work. The key is to have your backups from before you do anything so you have the supported route available if you encounter an issue. The only issue I have ever had doing this set of steps is that I didn’t set the file ACL’s correctly and the database engine threw Access Denied errors and failed to start until I fixed the ACL’s correctly. This can save you many hours of frustration and downtime trying to restore everything since the database files are already there and it is just some small copy operations to put them where you need them to be.
58 thoughts on “Downgrading SQL Server Editions”
Oh, if I had had this 10 years ago…. But that is how we learn. This article will help a lot of folks and keep them from having a much more difficult time downgrading.
~ No pithy quote to illustrate how urbane and witty I am.
Just did a downgrade from Enterprise 2014 to Developer 2014. Worked like a charm with Jonathan’s procedure.
Thanks Jonathan for another great article.
One quick question here. Would it also be preferable to go the Microsoft supported way and restore the system databases (master, model and msdb) after the downgrade is complete?
I’ve never had to, the entire point of this process is so you don’t have to start SQL Server in single user command line mode to restore system databases.
Has worked fine for me on several occasions. Just make sure you use the same service account for the downgraded edition or SQL server will be unable to decrypt any encrypted content in the original master database.
Thank you for discovering and sharing this simple method. It works great in SQL 2008, 2008R2 and 2012!
I have partially automated the downgrade process when dealing with lots of instances.
***WARNING***: It does not seem to work in SQL Server 2014 (with SP2 if it matters)!
Anyone tested this downgrade path with SQL Server 2016 Enterprise (SP1) -> SQL Server 2016 Standard (SP1)? I have read that this method doesn’t work with SQL Server 2014 and newer.
I have done this with 2016 Enterprise to Standard Edition, as long as your feature usage is correct and you are within the limitations of SP1 for CSI or IMOLTP if you use those features.
Thanks for the article.
Can this procedure be used for downgrading from SQL Standard edition to Developer edition?
Have you ever done it?
Yes, as long as you have the same build number and version at the end, this still will work.
Thanks for the fantastic article.
Is this step can be applied for downgrading from 2017 Developer edition to 2016 Developer edition?
No because the database internals are difference between 2016 and 2017 and upgrade is a one way process.
I think one important thing is missing – SMK Backup\restore: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-service-master-key-transact-sql
Note: you’ll have to use FORCE restore after old master DB copy over a new one.
Is this step necessary if the reinstall is on the same server and uses the same service account?
if i have to downgrade SQL Server Enterprise edition to a Developer edition on Always on Availability Servers what is the procedure?
Follow the steps of the blog post.
Thanks Jonathan for this post. We have a SQL Server 2016 Enterprise with Always on Configured, I would like to downgrade the Primary and Secondary ( Replica) both servers needs to be downgraded from Enterprise to standard edition. Not sure the impact of AG group and synchronization after the downgrade, probably I may need to drop the database from AG group and add it back. I understand I can add only one DB in the availability group due to the SQL Server standard editions’s limitations. If I wanted to add more database , I need to add more AG groups listeners for each DB. At this point we have only one DB in the AG group. Other than that do I need to consider anything. If someone did it already can you please share your high level game plan , thank you
Just used this trick to go from an accidental EE install to SE with SQL Server 2017 CU11. One thing to note, uninstalling does not delete the tempdb files. The new install will fail if the files are in the directory leftover from before. Just delete the leftover tempdb files before doing the new installation.
would this work effectively on an AG by applying the process to all VMs on the AG?
NB – This purely on a Dev / UAT AG, NOT live, Enterprise to Developer.
I know this is an old post, but in case it helps: if things were installed in a different path you might need to update columns subsystem_dll and agent_exe in msdb.dbo.syssubsystems, otherwise you will get errors similar to “The CmdExec subsystem failed to load” when running SQL Agent jobs after switching the system databases.
Just come across this excellent piece. One quick question – could the same be achieved by installing Standard Edition alongside the Enterprise Edition but then replace the system DBs on the Std Edtn with those copied from the Ent Edtn instance in step 4?
No, it’s not the system databases that effect the edition. The steps outlined here put the system databases from Enterprise Edition in Standard Edition and it works just fine. The edition is determined by the installation SKU and not by the system databases at all.
Could you please let me know if I can downgrade from SQL 2016 Standard to SQL 2016 Web
If it is blocked by Setup then you would have to do an uninstall and reinstall. I haven’t ever tried anything with Web Edition so I can’t say.
I am going to follow this approach to downgrade a 2005 instance next week. (Which will be retired towards year end but due to licensing reasons I have to downgrade it right now.) I just have one doubt. Current installed Edition is 64 bit whereas the setup I have with me is 32 bit. Just to test, I did a side-by-side install which went fine and build numbers match between the 64 bit and 32 bit version. Will soon find out if the in-place works fine too. Let me know if any word of caution …
Figured out current 64 bit enterprise edition is using 16 GB whereas if i downgrade to 32 bit standard it will use only 2 GB or 3GB max. If I enable AWE , how much can it use then ?
So, what when you made a downgrade but you did not make the backup of service master key 😀
What can you do?
You would have to recreate the non-decryptable items after the fact if you didn’t think of it until too late.
I found the 64 bit version. Your method worked like a charm. Saved a lot of effort. Thanks !!
Do you have any additional details on how to downgrade SQL Server with SSIS, SSRS and SSAS services configured.
You have to still do an uninstall and reinstall.
In case of SSAS downgrade, do we need to deploy and process again once server upgrade is done?
When cubes are deployed, we have to select the SQL Server Edition.
I have no idea, I don’t work with SSAS at all in my daily work. Sorry.
Hello Jonathan, I have a peculiar situation. I installed sql 2017 evaluation with clustering enabled. Now when trying to enter a key I am getting the error “The edition of the selected SQL Server instance is not supported in this SQL Server edition downgrade scenario. The source Evaluation edition and the target Standard edition is not supported path.” Please what can I do
Follow the steps in the blog post you commented on since that is the exact scenario the post was written about and there is nothing peculiar about it at all.
Will try going from SQL 2012 Enterprise to SQL 2019 Standard in a couple of weeks. So it is a downgrade/upgrade at the same time.
Will follow these instructions to the letter and keep you posted.
You would be better off doing a new installation and a side by side migration. Moving the system databases (master, msdb) from one version to another is not supported as far as I know.
Is that possible to perform in-place downgrade i.e. enterprise/standard to developer?
No, you have to uninstall and reinstall, as explained in this post.
Hey Jonathan, just wondering about moving Sql2k14 Enterprise to Standard.
The VM only has the SSISDB catalog installed it is just an Integration Services Server for apps that requires SSIS workloads in our DC
The steps outlined in this post will work.
How to do SQl 2014 Enterprise Edition Database to SQl 2017 Standard Edition restoration or migration activity? The databases are for ERP system.
The best way to accomplish this would be a side-by-side migration using a new server.
Can we Downgrade from SQL Server Standard Edition to SQL Server Developer Edition?
Thanks in advance,
You have to reinstall.
Any special processes or gotcha’s for going from Enterprise 2016 to 2016 Developer in an AG environment? haven’t found any info particular to doing this in an AG… I would think following this process, but using the normal sequence for patching AG’s would work, but not sure. Downgrade secondary first, fail over, and downgrade old primary.
I don’t know, I haven’t ever tried it, but the worst case would be you have to remove the AG configuration if it didn’t allow you to have the AG across different editions of SQL Server.
Really helpful one!
We did a downgrade from Enterprise to Standard (2016) and it works good.
Can you add a bit more info on ACL(access control list?). How to change the file ACL’s?
They are set in the folder properties in the OS.
How does downgrading Enterprise to Developer edition work with SSIS packages and Catalogs? Will everything be restored back after downgrade to developer?
If you follow the steps in this blog post it should work fine. Make sure you test before you do it on a live system though.
The uninstall won’t delete the user dbs mdf ldf files right?
I forgot, thanks. Seems like it won’t , i will test it out, but wanted to confirm, thanks.
Hello Jonathan, I have a peculiar situation. How does downgrading SQL 2014 Enterprise to SQL 2019 Standard edition work with SSIS packages and Catalogs? Will everything be restored back after downgrading to the Standard edition?
I don’t honestly know as I have never done this with SSISDB before.