Downgrade from SQL Server Enterprise Edition to Standard Edition

By:   |   Comments (39)   |   Related: > Upgrades and Migrations


Problem

I have a SQL Server that was installed as Enterprise Edition. I don't need the Enterprise features and I don't want to pay for the licensing. What is the best way to downgrade to Standard Edition?

Solution

I've came across a lot of SQL Server's in my career that were installed using the Enterprise Edition and never utilized the Enterprise features. Downgrading to a lower edition cannot be done in place. This tip will describe the steps needed to downgrade.

Create Database Backups

As always, before doing anything drastic (or non-drastic), BACKUP all the databases (system and user)! Also, if you have a development environment, please test on it first.

Check for Enterprise features

After backing up the databases run the following query on each database:

SELECT * FROM sys.dm_db_persisted_sku_features

This DMV will tell you whether or not the database is utilizing any of the Enterprise features. You can find more information regarding this DMV from this tip.

Check Version and Build Number

Because you never know when an install may fail, check the version and build number of the current SQL Server. After the downgrade, you will need to bring the new SQL Server back to the patch level. You can view this information by executing the following command:

SELECT @@VERSION


Check Version and Build Number

Save the results to a text editor.

Decision...

There are two different directions we can go from here.

  1. We can do the traditional uninstall Enterprise, install Standard, patch, restore databases
  2. Or we can do what I call "The Jonathan Kehayias" approach. (I saw this method awhile back from a forum post by Jonathan):

Copy System Databases

Shutdown the SQL Server service and copy the master, model and msdb database files (.mdf and .ldf) to another location. We will need these later:

hutdown the SQL Server service and copy the master, model and msdb database files


Copy System Databases

Uninstall SQL Server

Uninstall SQL Server from Control Panel:

Uninstall SQL Server from Control Panel

You only need to uninstall the Instance (includes SSIS, SSAS, SSRS, SSDT). There is no need to uninstall the Shared Components (SSMS, Client Tools, and Connectivity).

You only need to uninstall the Instance (includes SSIS, SSAS, SSRS, SSDT)


There is no need to uninstall the Shared Components (SSMS, Client Tools, and Connectivity)


Reboot

Reboot

Reboot the server. After rebooting, browse to the location of the data files and you will notice that the user databases weren't removed with the uninstall, but the system databases were. This is why we copied them in the step above:

After rebooting, browse to the location of the data files and you will notice that the user databases weren't removed with the uninstall, but the system databases were.

Install New SQL Server Edition

Enterprise Edition should be completely removed from the system now. Insert/Mount the media for SQL Server Standard and install. The install should be pretty straight forward. To make things easier in the end, make sure the name of the instance remains the same and the Data Directories point to the correct location.

Once SQL Server is finished installing, open SQL Management Studio and connect to the newly installed instance.

If you expand Databases, Security/Logins, SQL Server Agent/Jobs, etc. you will see it looks like a fresh copy of SQL Server.

If you expand Databases, Security/Logins, SQL Server Agent/Jobs, etc. you will see it looks like a fresh copy of SQL Server

Also, if you run the following command you will see that the version has changed to Standard Edition:

SELECT @@VERSION


Also, if you run the following command you will see that the version has changed to Standard Edition

Install SQL Server Patches

In this example, you will also notice that the build number has changed from 11.0.3000.0 to 11.0.2100.60 so I will need to install the correct patches to bring this server back to 11.0.3000.0.

(11.0.3000.0 is SQL Server 2012 SP1)

Download the correct patches and install on the server before doing anything else.

Once the patches are installed check the server to make sure it's at the same build number as it was before.

Copy System Databases

Shutdown the SQL Server service and copy the master, model and msdb database files (.mdf and .ldf) from the location you saved them in to the new location for the new install.

Copy System Databases


Shutdown the SQL Server service and copy the master, model and msdb database files

Start SQL Server

After copying the system files to the new location you can start SQL Server again:

After copying the system files to the new location you can start SQL Server again

Once SQL Server comes back online, you should see all the databases, logins, jobs, etc. back to normal:

Once SQL Server comes back online, you should see all the databases, logins, jobs, etc. back to normal
Next Steps
  • The example I showed was downgrading Enterprise to Standard, however, this should work in any downgrade scenario
  • Starting in SQL Server 2008, Microsoft added an "Edition Upgrade" wizard to help with upgrading editions. You can view more information from this tip.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, September 20, 2023 - 10:54:26 AM - Michael J Back To Top (91583)
Hi ! Thanks for the topic ! I have a question :-)
At the risk of perhaps saying something stupid,
We are not talking about the database resource (mssqlsystemresource.mdf and ldf).
Shouldn’t it be included in the databases to be copied and restored?
Is there a situation or should it be included?
Thanks Very much.

Monday, July 24, 2023 - 12:07:03 AM - church Back To Top (91424)
this worked to the T for migrating from 2019 enterprise to 2019 standard. thanks!!

Sunday, December 25, 2022 - 5:10:25 PM - Shuaib Khan Back To Top (90781)
Great job. Worked perfectly. But my SQL Server Agent does not start it gives an error stating that "SQLSERVERAGENT service started and then stopped (ObjectExplorer)" - Any help

Monday, February 15, 2021 - 7:23:26 AM - Yohan esteban taborda Back To Top (88240)
Excelente! Funciono Gracias.

Tuesday, March 31, 2020 - 3:05:32 PM - Susan Back To Top (85229)

I followed these steps and I was unable to restart the service after copying the system dbs.  Attempting a repair and that isn't working. 

Saturday, March 7, 2020 - 11:55:17 AM - Keith Back To Top (84998)

This worked great for SQL 2017.  Thank you!

Wednesday, March 6, 2019 - 3:23:18 AM - Sam Cheung Back To Top (79200)

It worked perfectly! Many thanks for your sharing.

Friday, December 7, 2018 - 10:38:16 AM - Andrew Back To Top (78425)

My customer wants to upgrade SQL 2012 Enterprise to SQL 2017 Standard Edition. Will this procedure work in this scenario? The customer only has the binaries for SQL 2012 Enterprise and 2017 standard. This means I can't downgrade the edition in 2012 before doing an in place upgrade to 2017 OR upgrade 2012 enterprise to 2017 enterprise prior to downgrading the edition to standard. Any ideas are appreciated!

Saturday, April 21, 2018 - 1:09:13 PM - Rafael Bahia Leandro Back To Top (75749)

 

Followed these steps today to perform a downgrade operation. They worked like a charm. The only thing that was missing is that I had to give the right privileges to the service account after installing the new instance. If you do not do this, you may get OS file access denied errors when the service tries to open the database files. I also had to manually reconfigure SSRS web services manually through the SSRS configuration manager.

Thanks for this post. It saved me a loooot of time.

Thursday, January 4, 2018 - 4:20:25 AM - Tom Newman Back To Top (74746)

 

Great article

 

just thought I would add something useful.

 

I downgraded the SQL version from Enterprise to Standard using the above process; all worked. ..... but the TCP IP configuration was reset. the configuration for this is in the registry as far as I know.

 

We have a CRM server that sits beyond the firewall that is configured to use a specific client connection port.

 

Something to add to the article would be a step to take screenshots or notes outlining the TCP/IP configuration. You could even export the registry settings. I had to revert the server take the TCP/IP settings and re apply. All was fine after this... well seems to be !!

 

Thanks again

 

Tom

 

Thursday, December 28, 2017 - 11:49:25 AM - Atul Back To Top (74541)

 

Very helpful. worked perfectly. Thanks Brady cheers!

Thursday, December 21, 2017 - 7:13:38 AM - Giamaica Back To Top (74306)

 I followed all the instructions but my user database are in suspect mode. it's because of the enterprise features they still have. how can i remove those features and start using them in a Standard instance?

 

thanks

Wednesday, November 29, 2017 - 3:55:27 AM - Barak Back To Top (73397)

great article, simple and works like a magic!

Tuesday, September 19, 2017 - 4:31:31 AM - Monwa Back To Top (66405)

Hi There

 

Thank you for the above mentioned tips to downgrade from enterprise to standard. However, i have an issue with the new install - "Instance name already in use.To continue specify a unique instance name".  Since you recommended to keep the same instance name as the old one.

Thursday, April 13, 2017 - 12:42:26 AM - Charlie Arehart Back To Top (54778)

Thanks, but you never say here what "The Jonathan Kehayias approach" is, and the link is just to his blog, not a particlar post.

If there may be a means to do such a downgrade without an uninstall/reinstall, even if "risky", I'm interested to hear what it may be and would be willing to give it a shot in a VM. Thanks.

Monday, February 27, 2017 - 3:55:43 PM - Ylann Back To Top (46741)

 Worked Perfectly! easy to follow, great job!

 

downgraded from 2012 standard to Express

 

Thanks!

 

Wednesday, December 21, 2016 - 8:01:56 AM - Julian Cook Back To Top (45018)

thank's this worked for me,  I found that it helped to use this as part of an upgrade process so I was going from 2008 to 2016, then I could download enterprise and developer editions at the same time and know that they were both the same versions, I upgraded the server to 2016 then did the downgrade to developer.

 

I did at first try patching and it seemed to work however I was unable to use Microsoft.SqlServer.Types assembly, I was not sure what the fault was 

 

 

Friday, December 2, 2016 - 4:46:19 PM - Tad Osborn Back To Top (44892)

Worked perfect.  Thank you! 

Thursday, November 10, 2016 - 8:30:50 AM - David Taylor Back To Top (43738)

Legend! This worked a treat and I have very limited SQL experience. Thanks a lot! 

 

Sunday, November 6, 2016 - 9:46:26 AM - Matthew Back To Top (43707)

Hi Brady,

Thank you for this. I used the process to downgrade 3 2012 SQL Server Business Intelligence Editions to Standard so I could then upgrade them to SQL Server 2014 Standard. It worked perfectly and saved me a major headache.

Cheers,


Matthew 

 

Tuesday, August 9, 2016 - 2:41:29 PM - MDG_DBA Back To Top (43091)

Will the above bring "replication" into the Standard edition build?  How about DTS packages / Agent jobs? 

 

Tuesday, July 19, 2016 - 5:51:11 PM - DBA Doug Back To Top (41924)

 Most excellent tip - saves time - saves hassle - works for SQL 2012 Ent to Std Ed without so much as a hiccup.

 

Wednesday, June 29, 2016 - 9:36:13 AM - AU Back To Top (41781)

 

 

Hi Brady,

 

Thanks for the tip and steps, could you please confirm that the method works good for the SQL Server 2005 version also.

 

Thanks,

AU

Thursday, May 12, 2016 - 6:01:37 AM - Romeo Back To Top (41466)

 

 Hi Brady,

sorry for my Englisch, I'm from Germany.

Yesterday I had to downgrade an SQL Server 2008 R2.

It was the first time for me to do it and there was no time to prepar.

I found your tip and it was so helpful.

Thank's a lot!!!

Romeo

 

 

Tuesday, February 2, 2016 - 3:17:39 AM - Dominik Back To Top (40570)

 Hi,
thank you for that article I will use it on friday (-:

One question. We have two instances one 01 is enterprise edition the other 02 is standad edition. do I have to uninstall both instances to downgrade to the std edition?
Would be great to here from you .. Thanx
Dominik

 

Wednesday, October 21, 2015 - 3:50:27 PM - Daisy Back To Top (38955)

I was able to downgrade the edition and verified using "select @@Version" that it's "Standard edition".  However, in the registry, HKLM\Software\Microsoft\Microosoft SQL Server\110\Tools\Setup, it's still "Enterprise Edition".  Is there a way to fix this, as the scanning tool is picking it as Enterprise?

Thanks,

daisy

Tuesday, October 28, 2014 - 5:05:30 PM - jason Back To Top (35101)

Hi, I am a SharePoint Admin and our SQL team is about to downgrade our SQL server from Enterprise to Standard.  I have heard that there is some risk to the functionality of SharePoint service applications by doing this.  Our SQL team is telling us that they are confident that none of the Enterprise features are being used, however I have read reports that SharePoint service apps will still use these features and once they are gone it will break.

Can you confirm your experience in this regard?  Will service applications be impacted?  Will it be all of them or just selected ones?

Whatever detail you can include related to the impact on SharePoint would be appreciated!


Thanks!

Friday, September 12, 2014 - 11:01:35 AM - Chuck Fox Back To Top (34508)

Monty,

You forgot to use the database in the sp_MSforEachDB command.  If you did USE [?]; prior to the select, all would have been well.  As it is all you tested was the database currently in use and you tested once for each database on the server. 

Your Friendly Neighborhood DBA,

Chuck

Tuesday, August 19, 2014 - 5:30:36 AM - MONTY Back To Top (34185)

Tried this procedure on a cloned SharePoint Server with SQL server installed on the same VM

I went through the process to check each database on the server - and all came back as not using SQL enterprise features (SQL 2008 R2 SP1)

Disclosure: I used the command below to check all databases on the server.

 sp_MSforEachDB 'SELECT * FROM sys.dm_db_persisted_sku_features'

which said none of my databases were using enterprise features.

While the process did work to downgrade to Standard, and all my logins, etc was recovered - two databases did not mount in Standard edition. Event log IDs 909 and 905 saying that they were using Enterprise features (data compression) etc -

TIP: further testing on the production system show that this command 'SELECT * FROM sys.dm_db_persisted_sku_features ' must be run individually and not using the sp_MSforEachDB procedure ... :(

Other things that broke or that need to be considered:-

Reporting Services configuration - had to be reconfigured to look at the existing database. I did make backups of distribution, master, msdb, model, SSISconfig, ReportServer, ReportServerTemp and restored them as part of the procedure above. When installing SQL standard, for reporting services, I said install but 'don't configure - I expected restoring the reportserver DB would have done the 'necessary' legwork.

Also what about SSIS, Analysis servers and Integration ??

More research needed - but a great tip for basic SQL installations.

Sunday, August 17, 2014 - 12:50:46 AM - Ken Back To Top (34170)

This method leaves out the bits of SQL Server configuration that are not stored in the system databases. Among other things, this would mean configured Alerts, Alert System configuration, and Operators will not be part of your downgraded installation. 

 

Is there a tested and/or production safe method of downgrading, while maintaining these configurations as well?

Tuesday, July 15, 2014 - 2:01:56 PM - J Stout Back To Top (32717)

This is a great article.

Does anyone know if the downgrade leaves a breadcrumb to allow MS to know that you had Enterprise at some point? Just curious as to if this causes issues with audits.

Thanks,

JS

Thursday, June 26, 2014 - 10:19:44 AM - Steve Back To Top (32415)

Good article and the method is actually very old, going back to early Sybase days. One thing though is to make sure that when you are installing the new version of SQL Server, that you put everything into the same footprint as the original installation. Otherwise when you copy in the original system databases, things may not come up. Additionally, I make a copy of the newly created system databases just in case. There have been times where changes were necessary for everything to come up on the original system databases. You will need these databases to make the changes. So don't just overwrite them.

Wednesday, June 18, 2014 - 1:40:04 PM - Amit Back To Top (32297)

Is this production safe??

Any pointers on how to go about SQL Server 2008 R2 Enterprise to SQL Server 2012 Standard upgarde on PRODUCTION??

 

Wednesday, March 19, 2014 - 3:41:10 PM - pcmm Back To Top (29816)

That's this saved me a bunch of work from another Admin who installed Eval versions of Enterprise and then left the company!! 

 

 

Friday, January 31, 2014 - 1:28:53 AM - Prof.Toh Back To Top (29290)

I've another way for downgrade SQL Server version from Enterprise to Standard version with some automate script

 

Let's see in

http://asktoh.blogspot.com/2014/01/how-to-downgrade-microsoft-sql-server.html

Monday, January 20, 2014 - 5:07:08 PM - Brady Back To Top (28153)

Hi Tim,

I'm assuming you are trying to do this on a SQL 2005 or below server? I have not tested an upgrade using this method so I'm not sure if it would work. I'll try to set this up in a test environment this week and let you know.

Monday, January 20, 2014 - 3:33:27 PM - Tim Cullen Back To Top (28152)

Hey Brady:

 

Good article. Does the concept of save databases, uninstall SQL, and then re-install SQL apply for upgrade to Enterprise from Standard?

Thursday, October 24, 2013 - 9:18:59 AM - Brady Back To Top (27263)

Paul,

Check out this link for SSIS:

http://support.microsoft.com/kb/942177

For SSAS, you can use the registry or connect to your SSAS instance using SSMS, right click the server, click Reports, Standard Reports.

 

Thursday, October 24, 2013 - 6:06:26 AM - Paul Back To Top (27258)

Very useful article BRady and relevant to what I'm looking at now for upgrade options to 2012.

As I don't know the full history of the SQL estate I've inheritted - do you know if there is a quick way of ensuring SSIS/SSAS aren't using any Enterprise features? Cheers Paul

 

 

 














get free sql tips
agree to terms