Problem
Have you ever needed to upgrade or downgrade the SQL Server edition you already installed? Maybe you currently have Enterprise Edition and have discovered that you can save money on licensing if you downgrade to Standard Edition. These steps will walk through both scenarios of upgrading or downgrading the edition and demonstrate how to complete either option.
Solution
The ability to upgrade your SQL Server Edition is built-in to the installer. We will walk through the steps to complete this action.
The downgrade operation is a bit more complicated and requires some backups and uninstallations. We will walk through all the necessary steps to complete the Edition downgrade and have your system back online with all the original settings/configurations.
Edition Upgrade in SQL Server
Let’s get started with the Edition Upgrade.
Currently, the SQL Server instance is running Standard Edition as shown above.
Locate the Setup.exe and launch the SQL Server Installer for the version that matches the current server instance.
Choose Maintenance > Edition Upgrade.
- When prompted, enter the Product Key information or accept the Product Key that is populated automatically.
- Select Next then Accept the License Terms and select Next again.
- If your server has multiple instances, choose the one where you want to upgrade Editions. If you only have one, leave the defaults and click Next.
- Finally, choose Upgrade to start the Edition Upgrade process.
- You will see a Results section and all the features should show a Succeeded status when the process finishes.
- Click Close and then Exit the Installer.
Proceed back to SSMS and login to the SQL Server instance. View the properties of the SQL Server instance. The results should show that the SQL Server Edition is now Enterprise as shown below.
The Enterprise Edition of SQL Server is now running this instance and everything is fully functional.
Downgrade SQL Server Enterprise to Standard Edition
This section is going to depend on how comfortable you are restoring the SQL Server master database. Downgrading SQL Server Editions using this method might be risky for some individuals. However, just like "never shrink your data files", there are circumstances when such an action is warranted.
The following steps show how to downgrade the SQL Server edition while still maintaining the information in your master database.
This screenshot above shows that there are currently two SQL Server logins, one user table, and two user stored procedures in the existing Enterprise Edition of SQL Server instance.
The steps below will prepare the server downgrade to Standard Edition.
First, perform a full database backup of the master database using a command similar to the below or using SSMS.
BACKUP DATABASE [master] TO DISK = 'E:\BACKUP\MasterEnterpriseEdition.bak'
The next set of steps will sound aggressive, but they are necessary and safe. The uninstallation of SQL Server will now be executed on the server.
- Navigate to the Control Panel and choose Uninstall a program under the Programs section.
- Find Microsoft SQL Server 2016 (64-bit) or the appropriate version on your server.
- Click Uninstall/Change, then click Remove.
- The SQL Server Installer will launch and list all the SQL Server Instances currently installed on the server.
- Choose the SQL Server instance you want to downgrade and click Next.
- Click Select All to remove all SQL Server components from the server for this instance and click Next.
- Now click Remove to begin the uninstallation operation.
- You will see a Results section and all features should show a Succeeded status when the process finishes.
- Click Close, then refresh the Programs and Features windows, and you should not see SQL Server in the list.
- At this point, a server Restart is required.
Locate the setup.exe and launch the SQL Server Installer for Standard Edition.
Navigate through the prompts as would be normal during a typical SQL Server installation.
Apply all the SQL Server patches required to get to the same patch version as your Enterprise before the uninstallation.
The image above is an example showing a blank/fresh SQL Server Standard Edition installation.
Perform a full database backup of the master database using the below command or use SSMS.
BACKUP DATABASE [master] TO DISK = 'E:\BACKUP\MasterDefault.bak'
From the SQL Server Configuration Manager, stop the SQL Server services.
Open a Command Prompt window as Administrator and navigate to the Binn directory for the SQL Server instance (example: E:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Binn).
Run the command:
sqlservr.exe -m
SQL Server will be launched in single-user mode from the Command Prompt by running this command.
Open another Command Prompt window as Administrator and run:
SQLCMD -S localhost
Executing the above command will connect to the SQL Server Instance and allow you to execute T-SQL commands.
Use the backup taken earlier of the master database from the Enterprise Edition version in your restore command as follows.
RESTORE DATABASE master FROM DISK = 'E:\Backup\MasterEnterpriseEdition.bak' WITH REPLACE;
Once the restore command is successfully completed, close both open Command Prompt windows.
From the SQL Server Configuration Manager, start the SQL Server services.
Once the SQL Server services are started, the restore that was done will have recovered the Logins, Table, and Stored Procedures from the backup taken of the Enterprise Edition to the new Standard Edition server.
Conclusion
These steps walked through the process of Upgrading a stand-alone SQL Server instance from Standard Edition to Enterprise Edition. The opposite has been achieved by taking steps of Downgrading Enterprise Edition to Standard Edition without losing any of your system-level settings.
Resources
- MS Docs – Restore master Database
- MSSQLTips – Options to Restore master Database
Notes
- As with all recommendations, you should attempt these steps in a non-production environment first.
- Only attempt the Edition Downgrade option on a server where the same Server Name is to be used.
Next Steps
- Check out these other SQL Server install and uninstallation tips.
