Migrate SQL Server Database to Lower Version – Restore Old SQL & Downgrade
The blog will discuss the stepwise procedure on how to migrate SQL Server database to lower version using 3 methods.
Scenario: Suppose a situation in which a user of SQL Server Database upgrades its SQL Server from lower version to higher version and realizes that its application is not functioning properly after the upgrade. Evidently, the user wishes to roll back the upgrade by downgrading the SQL Server Database version to its previous version. However, he/she is unable to attach the database or restore the backup of database even after setting the compatibility level to lower version.
Tabe of Content
How to Restore Database from Higher Version to Lower Version in SQL Server – Critical Error
The following error message will be displayed when the user tries to restore or attach the database. It happens as the SQL Server is automatically upgraded to higher version and it does not allow user to restore database from higher to lower version.
Note: The scenerio/problem explained above is one of the many problems faced by our clients. If you generally want to downgrade SQL Server 2019 to 2016 or any other version, you can follow these given solutions.
Error Reason
To understand why such an error occurs, users must know that the database changes its structure in every SQL server version known as cumulative updates. Sometimes a little bit & sometimes a massive change is observed by the experts globally. This is the major reason why users have to face this error.
How to Restore Database from Higher Version to Lower Version in SQL Server – User Queries
We have seen various users struggling with this issue. It’s a common query for users to upgrade their database. However, when users want to downgrade, there are not many answers available. We’ve got some user queries from which we can definitely take some help.
User Query – 1. Backup & Restore Old SQL Version
User Query – 2. Downgrade SQL Server 2019 to 2017
User Query – 3.
General Solutions As Per Various Users
- This is not possible directly. Microsoft does not offer users to do this. However, if they can make changes in the data type & structure, it can be easily done. The right technique is quite useful for data administrators.
- There is no way users can do this task. What they can do is just copy the entire data from that database & then paste it into the lower version database with some modifications.
- Users can do the script generation manually or they can opt for any automated software. Other than that, there is no possible way for users to get the perfect solution.
Move SQL Server Database to Old Version Using Advanced Software
As you know, handling SQL can be tricky as it involves so many complex procedures. Also, nobody wants to mess up with their data. So it is suggested that you go for professional software that will turn laborious and wearisome tasks into unchallenging efforts. Hence, the below-mentioned solution can easily help users learn how to migrate a SQL Server database to a lower version with ease.
The Above SQL Migrator can transfer or Copy SQL Table from one database to another with schema, Functions, Views, Stored Procedures, Triggers, etc. Moreover, if any data gets deleted, the same software can help you restore the database.
Follow the Quick Guide here to migrate SQL Server database to lower version:
Step-1. Start the Software & then Click on the Open button.
Step-2. Select the Online or Offline Mode as per the choice.
Step-3. Preview the Database Objects before moving forward.
Step-4. Adjust the Export Settings as per your preference here.
Step-5. Click the Export button to downgrade SQL Server 2019 to 2017 & other server versions.
How to Migrate SQL Server Database to Lower Version Manually
The error occurs as SQL Server database files and backups are not backward compatible restricting restore of database created from higher SQL Server version to lower version. Also, below are some of the steps to migrate SQL Server Database from higher version to lower version:
1. Use Generate Scripts wizard of SQL Server Management Studio
In this step to downgrade SQL Server 2019 to 2016 & other versions, we will first script the schema of the desired Database on SQL Server 2012 instance to migrate the database to SQL Server 2008 R2 using Generate Scripts wizard of the SQL Server Management Studio.
- Launch Microsoft SQL Server Management Studio (SSMS) and Go to Object Explorer.
- Select the desired database and right-click on it.
- Click on Tasks and choose ‘Generate Scripts’
- Generate and Publish Scripts wizard will be opened.
- Click on Next & proceed to Choose Objects page
- On the page, choose the option ‘Script entire database and all database objects’ and click on Next to move to next Tab. i.e. ‘Set Scripting Options’
- Define the location to save the script file on the Set Scripting Options Tab
- Click on Advanced button to specify scripting options
- In Advanced scripting options, we will modify some of the options
Script for Server Version →SQL Server 2008 R2
Types of data to script → Schema and Data
Set Script primary key, Triggers, Indexes options True - Click on OK after making the changes in Advanced Scripting Options and go back to Set Scripting Options Page. Enter ‘Next’ to proceed to Summary Tab
- Click on Next on Summary Page after review the selections made.
- Lastly, click on Finish button to close the Generate and Publish Scripts Wizard. This is crucial after the successful completion of script generation process.
2. Connect to Lower Version, Run SQL Scripts to Create Database Schema
In the second part of learning how to restore database from higher version to lower version in SQL Server, we will connect to the lower version i.e. SQL Server 2008 R2 Instance and run the scripts generated earlier in the first part to create the database schema and copy all its data.
- We will go to Object Explorer and Connect to the SQL Server 2008.
- Select the script file created and click on open to use the script in SSMS.
- Change the script to define the location of the Database data and log files.
- Execute the script on create the database on SQL Server 2008 instance
- After the scripts have been executed successfully, refresh the database folder in Object Explorer of SSMS.
- Click on the database to check if all the data has been successfully downgraded from SQL Server 2012 to SQL Server 2008 R2.
Manual Method Drawbacks
There are several issues with the manual solution & this is why users mostly prefer using the automated solution. Let us have a look at the most common & dangerous ones.
- In case the higher SQL version uses some of the data items that are not supported in the lower version, users need to manually make changes in the code. This can be really hectic for users & might result in some data loss.
- This approach is time taking & requires constant attention from users which can spoil the user experience.
There are no advanced filters present in this tool that users need to speed up the task & make their entire process easier.
Conclusion
In the blog, we have discussed about the error when user of SQL Server Database upgrades from lower and higher version. Also experiences that the application is not functioning properly are also there. In such situations, we can use the scripts using Generate Scripts Wizard in higher version and execute the same scripts in lower version. The steps are given for the whole process to migrate SQL Server database to lower version.
It is advisable that users take backup of database before upgrading their SQL Server version. Since the migration process takes lots of time and storage, downgrading SQL Server Database must include professional software to migrate SQL Server from higher to lower version.
FAQs
Q-1. Is it possible to downgrade SQL Server 2019 to 2016?
Ans. As per Microsoft, it’s not possible to downgrade SQL Server 2019 to 2016 or other versions. It requires the transfer of data files to the desired version using SQL scripts which users find tough. Hence, it is possible with the help of an automated solution.
Q-2. How to downgrade SQL 2017 to 2014?
Ans. Completely uninstall your SQL server from your system & then install the preferred version. However, it won’t transfer your files to the old version but only let you use the old version. Data migration is needed here to get the appropriate results.
Q-3. Can I restore a SQL 2016 database to 2012?
Ans. Yes, there are two ways, for the manual one, take a backup of the files, uninstall the current version, install the required version & upload the files. For the automated, use the modern tool & you’re done.
Q-4. How do I overwrite an existing database in SQL Server 2012?
Ans. Go to Navigation, and Click on options. Select Overwrite the existing database option (WITH REPLACE) & then close existing connections to the database. Click on OK to finish.