Migrating SQL Server Database to Lower Version
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. 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.The blog will be discussing the step wise procedure on how can the user migrate SQL Server Database to lower version.
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.
Steps to Migrate SQL Server Database to Lower Version
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. 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 after the successful completion of script generation process.
2. Connect to lower version, run the 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.
In the blog, we have discussed about the error when user of SQL Server Database upgrades from lower and higher version and experiences that the application is not functioning properly. 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 of migrating 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 should only be considered as last option or one can use SQL Server Database Migration tool to migrate SQL Server from higher to lower version.