How to Restore Database from SQL Server 2014 to 2008 Version
Many SQL users want to downgrade their SQL Server database. In other words, they want to restore database from SQL Server 2014 to 2008. But SQL Server database is not downgraded compatible. That is why it is not easy to get back to a previous version of SQL. However, there are some ways that can be used to downgrade SQL database. This post will discuss such methods of downgrading SQL Server 2014 to 2008. Before going to the main discussion, we will have a look at a user query:
“I have recently upgraded to SQL Server 2014, but there have been some issues with the application since the upgrade. Now I want to downgrade my SQL database to its previous version. I believe this downgrade will solve the current issues. Sadly, I do not know the process of restoring database from 2014 to 2008. Kindly suggest me an easy method to downgrade 2014 SQL Server.”
Table of Content
Methods of Restoring SQL Database from 2014 to 2008
There are two methods by which 2014 database can be restored to 2008. One is by using Generate Script Wizard and another one is by using third-party tools i.e. SQL Backup Recovery. Here readers will learn both the techniques of restoring SQL Server 2014.
Method 1: Restore Database from SQL Server 2014 to 2008 Through Generate Script Wizard
In this method, Generate Scripts wizard is used to script the SQL database schema in a higher SQL database version. Then we get connected to a lower SQL Server version and run the script we have generated earlier. Thus we are able to copy data and successfully restore database from 2014 to 2008.
#Stage 1: First, we have to script AdventureWorks database schema in SQL 2014 Server. To do that, we will use Generate Scripts wizard.
- To begin with, connect to SQL Server instance of 2014 version, In “Object Explorer.” Then right-click on Adventureworks database and expand Tasks. Select the option “Generate Scripts…”.
- Generate and Publish Scripts wizard will open. Skip Introduction screen and go to Choose Objects page by clicking Next.
- On the next page named “Choose Objects”, select Script entire database and all database objects. Click Next.
- On Set Scripting Options page, choose the file name and location where you want the script to be saved. then Click on Advanced button.
- A dialog box called Advanced Scripting Options will appear. You have to change certain options on this box. Set the value True for “Triggers,” “Indexes,” and “Primary Key.” Also set SQL Server 2008 R2 as “Script for Server Version” and Schema and Data as “Types of data to script.”
- Click OK to close this dialog box and get back to Set Scripting Options page. Click Next on that page to proceed.
- On Summary page, review all the selections you have made and then click on Next button to start generating scripts.
- Once the script generation is completed, click Finish to close the wizard.
#Stage 2: Now, we have to run the SQL Script generated in stage 1 and copy data.
- Get connected to SQL Server 2008 in Object Explorer. Open previously saved SQL Server script in SQL Server Management Studio.
- You have to edit the script by specifying correct Adventureworks database data location and log files location. After that, run the script. This will create Adventureworks database on SQL Server 2008 instance.
- Once it gets executed successfully, refresh Object Explorer Database folder. You will see that the Adventureworks database has been downgraded.
Method 2: Restore Database From SQL Server 2014 to 2008 Automatically
It is understandable that most users do not want to go through such complex method. For them, there is an alternate method available in the market. Users can choose SQL Backup Restore Tool to seamlessly downgrade SQL Server database 2014 to 2008. This tool has a simple interface that anyone can use without any problem. It does not take much time to restore SQL database using this application. Users need to fill the destination server name where the database has to be restored. Then this software will smoothly downgrade the database to desired SQL Server version.
Step-1. Launch the Software in the system & Hit the Open button.
Step-2. Now, Add the Backup File to the software.
Step-3. Click on the Recover Button to proceed further now.
Step-4. Select the Destination SQL Server to restore files.
Step-5. Finally, Click the Export button to complete it.
Final Thoughts
If you are looking for solution to restore database from SQL Server 2014 to 2008, this post will be of great help. It discussed two different methods of restoring SQL database 2014 to 2008. The method using Generate Script Wizard is a complex one and takes a lot of time to be executed. So, it is suggested that users adopt the alternate method of using the third-party tool. This program allows you to revert back to the older version of SQL Server easily.