Migrate SQL Server Database to Lower Version – 3 Ways
The blog will be discussing the step wise 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
Error Message While Restoring the SQL Server Database
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 shift to Lower Version of SQL server, you can follow these given solutions.
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.
Migrate SQL Server Database to Lower Version in 5 Steps
- Launch the tool in your system to begin.
- Select Open to add your MDF data files.
- Scan files to repair any data corruption.
- Enter destination SQL server database.
- Click on Export to get your final results.
Move SQL Server Database to Old Version Using SysTools 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 a professional software that will turn laborious and wearisome task into unchallenging efforts.
SysTools SQL Server Migrator Software
The Above SQL Migrator can transfer or Copy SQL Tables data 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 Qick 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 at last & finihs the migration task.
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 Higher version
In this step, 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 & Copy Data
In the second part of the procedure, 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.
- Open the script created by going to File in SQL Server Management Studio →Open→File
- 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.
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.
Q-1. Is it possible to downgrade the SQL Server?
Ans. As per Microsoft, the in-place downgrading of the SQL server is not at all possible. 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.