How to Downgrade SQL Server 2016 to 2014 in an Efficient Manner

Nowadays, the requirements of the users keep on changing on daily basis. Sometimes, to use advanced functionality, people usually upgrade the version of SQL Server. But in a few situations, for some reasons, user want to downgrade the SQL Server edition application. Thus, to overcome this problem, generally, users want to lower the upgraded version by simply downgrading the version of SQL Server. However, when users downgrade SQL Server 2016 to 2014, they either would not be able to attach the database or restore it through the backup. But, the compatibility level of the database is set to downgrade the SQL Server version. While doing the same, a user receives the error message as below:

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'DatabaseName' cannot be opened because it is version 655. 
This server supports version 611 and earlier. A downgrade path is not supported.

What is the Possible Reason Behind this Error?

Whenever one tries to restore or attach the database from upper to lower version, it generates the above-stated error message. But, always keep in mind, SQL Server does not permit users to restore database from SQL Server 2016 to 2014. The reason for this is the mismatch of the data & their value fields. As it has become the most common issue faced by the users so, it needs to be fixed properly so that users can migrate SQL database to a lower version.

Hence, this post will explain the procedure that one can easily follow on how to downgrade SQL Server database 2016 to 2014.

Common Query Asked by the Users:

“My SQL Server installed in Enterprise Edition. But their features are not required and also it keeps on notifying me to pay additional charges for licensing. I don’t want to pay for it, hence I need to downgrade SQL Server database version. If anyone knows the procedure to downgrade SQL database, please let me know.”

Now, it’s no compulsory that users only want to switch from 2016 to 2014, users might need to downgrade SQL Server 2019 to 2016  or even downgrade SQL Server 2019 to 2014 version. Therefore, with the solutioned mentioned below, users can get the best solution.

Method to Downgrade SQL Server Database 2016 to 2014

The main reason behind this error message is that SQL Server files i.e., MDF, NDF, LDF, and backups are not compatible in lower versions. Because of the compatibility issue, a user is unable to restore database from SQL Server 2016 to 2014. Now, to downgrade the SQL Server database, there are multiple options available that one can use. Here, we will discuss the best among all i.e., by creating scripts wizard of SQL Server Management Studio. Follow the steps mentioned below:

• First of all, you have to script the database schema in the higher version of SQL Server with the help of Generate Scripts wizard of SQL Server Management Studio interface.
• After that, simply connect the older SQL Server version and run SQL scripts generated in the above step. This will help in creating the database schema and copying the data.

Stepwise Procedure to Downgrade SQL Server 2016 to 2014

In order to downgrade SQL Server database 2016 to 2014, simply follow the instruction discussed below:

Step 1: Use Generate Scripts Wizard of the SQL Server Management Studio interface

1. First of all, you need to script the schema of your database on the SQL Server 2014 instance with the help of Generate Scripts wizard
2. This will instantly start the Generate and Publish Scripts wizard. You have to click on the Next button to pass over the Introduction screen and move to the Choose Objects page
3. Now, from the Choose Objects page, simply select “Script entire database and all database objects” option. Then, click on the Next to move to the “Set Scripting Options” page
4. After that, specify the location where you want to save the script file on the Set Scripting Options page. Then, choose the Advanced button
5. Now, an Advanced Scripting Options dialog box will appear. Here, you can set Script Triggers, Indexes and Primary Key options to True. Then, set Script for Server Version to SQL Server 2014, and Types of data to script to Schema and Data. Last option is key as it will generate the data for each table
6. Click on the OK button and close Advanced Scripting Options dialog box. Then, simply return to Set Scripting Options page. Here, you have to click on the Next button and continue the Summary page.
7. Have a quick review on the Summary page and click on the Next to generate the script
8. As the script is generated successfully, click on the Finish button and close the Generate and Publish Scripts wizard.

Step 2: Connect to the SQL Server 2014

A user needs to connect the SQL Server 2014 and after that, you have to run SQL scripts that are created in Step 1 to create the database schema and to copy the data.

1. Here, in the Object Explorer, you have to connect to the SQL Server 2014. Then, open SQL Server Management Studio and open SQL Server script, which you saved in step 1
2. After that, you have to modify the script and specify the exact location for your database data and log files. When you are done with it, simply run the script and create the database on the SQLServer2014 instance.
3. When the process is completed successfully, you have to refresh the Database folder in Object Explorer. After performing all this successfully, the SQL Server is downgraded from SQL Server 2016 to 2014.

An Automated Approach to Downgrade SQL Server 2016 to 2014, 2012, 2008, Etc

As a user can see that the manual procedure to downgrade SQL Server 2016 to 2014 is quite complex and requires a lot of time. Therefore, for an easy and simple solution, you can switch to the third-party tool named as SysTools SQL Server Database Migrator. It is one of the best solutions to restore database from SQL Server 2016 to 2014 in just a few simple clicks.

Download Purchase Now

Download the software & then simply follow the five simple steps mentioned below:

Step-1. Launch the Software & Hit the Open button to begin.

Step-2. Now, Select the Online or Offline Mode for migration.

Step-3. Preview the Database Files to cross-check them.

Step-4. Enter the Destination Server & Other Settings.

Step-5. Now, Click on the Export button to finish off.

Conclusion

Depending on situations or reasons, the user needs to downgrade SQL Server Database 2016 to 2014 may vary. So, we have come up with manual as well as an automated approach to downgrade SQL Server 2016 to 2014. You can try any method as per your situation. Moreover, users can downgrade SQL Server 2019 to 2016 or directly downgrade SQL Server 2019 to 2014 if they need using the advanced utility.