Recently, after upgrading an SQL Server instance to SQL Server 2016, our developers noticed that the application was not functioning properly. To fix this we needed to roll back the upgrade by downgrading the SQL Server database to SQL Server 2014.
Unfortunately for us, SQL Server does not allow you to restore or attach a database from a higher version of SQL Server to a lower version. To get around this, I used the Generate Scripts wizard of SQL Server Management Studios (SSMS).
Here’s how I did it:
1. Script the database schema in higher version of SQL Server (in this case it is SQL Server 2016) by using the Generate Scripts wizard of SQL Server Management Studio (SSMS) interface.
2. Right Click on Your Database –> Select Task–> Generate Script…
4.On the Generate and Publish Scripts , Introduction page–> Click Next
5.On the Choose Objects page, choose Script entire database and all database objects–> Click Next
6.On the Set Scripting Options page, chose the location that you would like to save the script file–>Click Advanced button
7.On the Advanced Scripting Options page, set Script Triggers, Indexes and Primary Key options to True, Script for Server Version to lower version of SQL Server (in this case it is SQL Server 2014), Types of data to script to Schema and Data.
8. On Summary page review your selections–> Click Next to generate script.
9. After the script has been generated, click Finish button.
10.To generate the new database on the lower version of SQL Server (in this case it is SQL Server 2014), connect to SQL Server 2014 Instance and run the script you have just created.
