Search This Blog

Thursday, February 25, 2016

Downgrade from Microsoft SQL Enterprise to Standard & Keep all settings / databases

I had a request to upgrade one of our SQL Servers from SQL 2005 to SQL 2008 R2.  Upon looking at the SQL 2005 server I realized it had the Enterprise version rather than the Standard version installed.   We are not using Enterprise functions and our license is for Standard so I needed to get this server to the Standard version before upgrading it to SQL 2008 R2 as you cannot upgrade from Enterprise to Standard, it must be Standard to Standard or Enterprise to Enterprise.  I would imagine this would work for any type of downgrade such as Developer to Standard, etc.

I successfully uninstalled 2005 Enterprise, reinstalled 2005 Standard and then upgraded to 2008 R2 Standard.  All databases, logins, settings, etc were still present when completed.

To change from Enterprise to Standard:

First and foremost, make sure you have a full backup of your server. I am not responsible if you hose your server, back it up.  :)

  1. Document server collation (right click server in Management Studio > Properties) as you must set it to the same when you install the Standard version later.  Mine was set to SQL_Latin1_General_CP1_CI_AS.  This is important as it determines how your server orders items in the databases so it's critical it's the same during the Standard install.  
  2. Document Version.  This tells you what the latest service pack and/or update that is installed on the server as you need to get Standard to this same version later.   Here is an article on how to determine your version number.  Find the year of SQL server and click the link to the right of it that says SQL Server xxxx Builds and there you can find out which updates were installed last.  https://support.microsoft.com/en-us/kb/321185


  3. Stop all the SQL Server services
  4. Copy all of the system databases to another location as a backup for later as we will need to copy them back once we have reinstalled the software.  My system databases are stored apart from the others so I backed up the entire Data folder that contained them.  The databases I backed up were templog, msdblog, ssdbdata, model, mastlog, master, and dismdl.  Be sure to backup both the ldf and mdf files.
  5. Uninstall SQL Server completely.  Uninstall components but document which components.  I saw another post where someone said to leave the shared components but you should not do this as I ran into an issue with the upgrade to 2008 R2 in that it detected that Integration Services were originally installed with the Enterprise version and this caused the upgrade advisor to fail the upgrade.  I had to later go back and remove this component and then reinstall it to fix this issue.

  6. Install SQL Server Standard.  Remember to choose the correct collation.  I should note that when I reinstalled mine that it listed the version as SQL_Latin1_General_CI_AS and did not include the words CP1 as in my original setup.  Once installed though it listed it correctly so just make sure you get the options correct.  Also make sure to install the correct components that you uninstalled previously.

  7. Download and install the updates necessary to bring your newly installed SQL version up to the same one as it was previously.
  8. Stop the SQL Server services if they are running
  9. Copy all the system databases back into the SQL Server folder where they were previously, you can just overwrite the ones that are there from the new install.
  10. Start the SQL Server services
  11. All databases should now be present and you should be able to create or use an ODBC and connect to them to test.  
Once I completed these steps I upgraded to 2008 R2 Standard through normal procedures.  As I said previously I had originally left the shared components installed and this then triggered an error that meant I could not upgrade because it was not an approved path.  After reading through the logs I determined this to be the fault of Integration Services so I went to the control panel > Programs and Features and I clicked Change on the 2005 SQL Server install and uninstalled IS, then reinstalled it by running the 2005 Standard install again and only choosing Integration Services.  Once complete I ran 2008 R2 Standard upgrade and everything went smoothly.

No comments:

Post a Comment