Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Downgrade from SQL Server Enterprise Edition to Standard Edition


By:   |   Read Comments (27)   |   Related Tips: More > Upgrades and Migrations

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

great article, simple and works like a magic!


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

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

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

 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

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 02, 2016 - 4:46:19 PM - Tad Osborn Back To Top

Worked perfect.  Thank you! 


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

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

 


Sunday, November 06, 2016 - 9:46:26 AM - Matthew Back To Top

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 09, 2016 - 2:41:29 PM - MDG_DBA Back To Top

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

 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

 

 

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

 

 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 02, 2016 - 3:17:39 AM - Dominik Back To Top

 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

 

 


Learn more about SQL Server tools