/
Restore Database to older version of SQL server

Restore Database to older version of SQL server

Owned by Thijs Froon
Oct 16, 2020
3 min read

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:

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 to 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.

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.

NOTE: Depending to database size you might encounter an issue with low buffer size, if this happens, the query to create the database and tables can be run separately from the population part of the script.

Source: https://www.sqlserverlogexplorer.com/migrating-database-to-lower-version/