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 Server Migration Tool. Here readers will learn both the techniques of restoring SQL Server 2014.
Prerequisites to Prevent Disaster During the Process
- This isn’t just a restore or recovery process in a database. Hence, it is beneficial to consider this as a migration process for data in SQL Server 2014 to SQL Server version 2008 and further carry out the process with complete precision.
- Before initiating the process, it is important to lock the schema and put the source database, i.e., SQL Server 2014 database to read-only mode so that no data is changed or hampered during the migration process.
- Never run the process directly on the production environment, as any issue or challenge can lead to bigger issues including permanent data loss in the database. It is better to run the commands on a clone database first.
- To avoid any issues in the future regarding the process and why it took place, it is crucial to document the entire process.
These are some of the best practices database administrators can follow to avoid any disasters during the migration process.
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 with the restore database from SQL Server 2014 to 2008 process.
- 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 Server Database Migrator 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 to restore the database. Then this software will smoothly downgrade the database to desired SQL Server version.
Step-1. Launch the Software in the system.
Step-2. Now, Choose from Offline or Online to connect with the source server.
Step-3. After connecting to the server, preview the scanned data and click on Export button to restore database from SQL Server 2014 to 2008 method.
Step-4. Select the Destination SQL Server and add authentication details to restore files.
Step-5. Choose database objects to be restored and 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 for execution. So, we suggest users go for the alternate method of using the third-party tool. This program allows you to revert to the older version of SQL Server easily.