Life is beautiful when you have good friend around you. If you are regular reader of my blog, you would understand what kind of friends I have. Anyways, as usual, another friend, another interesting question and another blog topic.

My dear friend called and told that he has enterprise edition Installed on his laptop and it was “accidental" install. His end goal was to use developer edition but “someone” installed enterprise. Since he has done a lot of work in this database and wanted to make sure his data is not lost, he called me for advice. In short, he wanted to change his edition from enterprise to developer with no data loss.

Well, upgrading edition of SQL Server is easy (my earlier blog) and downgrade is not “so” easy. We are calling it as “downgrade” because its not listed as supported upgrade on books online. If you are using features which are not supported in edition where we want to downgrade (like enterprise to standard) then it’s tricky as we have to remove those features first. Note that Developer and Enterprise are exactly the same so my friend was little safe. I must confess that I am not the inventor of this “downgrade trick”. It is well known and tested by many earlier. I am just writing them down so that it would help someone in future. Standard disclaimer applied on this blog post.

Before attempting the steps we should take backup of ALL databases (system and user). Make a note of location of various component (system databases, shared components) in operating system. The trick would work only if the paths are not changed.

• Open Reporting Service Configuration Manager and take a backup the encryption keys.
• Use SQL Server Management Studio and connect to SQL. We need to run the following query on each database.  
  
  SELECT * FROM sys.dm_db_persisted_sku_features
• Above DMV will tell us if the database is utilizing any of the “Enterprise only” features (like Partitioning, Compression etc.). If there is any such feature which is not supported on destination editions then we should remove it otherwise database would not come online after last step. In my friend’s case he was using compression but it works in developer edition as well so there was no need to remove that. If there are no rows returned in any database then we are good to move next.
• Run SELECT @@VERSION and make a note of exact version and build number (like 11.00.3000 –  which is SQL 2012 + SP1) . This is needed because we have to upgrade the newly installed SQL instance to the exact same build later.
• Stop SQL Server service. Copy all the database files. We need to copy all mdf, ldf and ndf files for system and user databases.
• Now we can safely uninstall SQL Server. You should take a screenshot of the “Select Features” screen while uninstalling so that you could be certain to install the correct features when installing again in later steps.
• Reboot, if necessary.
• Now, install New SQL Server Server instance having SAME name and SAME path as of earlier instance.
• Since we want to reuse databases, we need to apply SQL Server Patches so that the version matches with what we had earlier. (11.00.3000 in my example)
• Stop SQL Server service.
• Take backup of current files to make sure we can revert to this state.
• Move all databases files back to their original locations. We also need to replace the system database files with the previous one.
• Now we need to Start SQL Service again.
• Verify that databases are online and healthy again with new Edition!

Someone said that this is old trick and used in Sybase as well. And my answer – hmm.. Yeah, I am old now.