How Do I Restore a SQL Server Database Backup to an Older Version of SQL Server

SQL Server doesn’t allow taking a database backup from a higher version and restoring it to a lower version. And, you would likely encounter the following error when trying to downgrade a database to an older version of SQL Server:

Here, the error message clearly states that therestore of database failedas the backup was from SQL Server version 2008 but was restored on SQL Server 2012 version.

Why Such a Situation Occurs?

Even if the database objects, like tables, stored procedures, views, etc., are compatible with the lower version of SQL Server, restoring the backup taken on a newer SQL Server version will still fail. This happens because of the database changes in each version of SQL Server, such as cumulative updates.

What You Can Do to Restore a Database to an Older SQL Server Version?

You can try restoring a SQL Server 2012 (or other versions) database to an older version of SQL Server (2008 R2 or lower) by following these steps:

Step 1: Generate Scripts in Higher Version of SQL Server

Step 2: Run the SQL Scripts in Lower Version of SQL Server

Let’s discuss both these steps in detail.

Step 1: Generate Scripts in Higher Version of SQL Server

In this step, we will use the‘Generate Script’wizard for generating scripts in a higher version of SQL Server Management Studio (SSMS). For example, in our case, we will use SQL Server 2012 Management Studio. The detailed steps are as follows:

In SQL Server 2018 Management Studio (SSMS), right-click a database, and clickTasks > GenerateScripts.

On the‘Generate and Publish Scripts’window, click the‘Next’button.

On the'Choose Objects'screen, the'entire database and all the objects'option is selected by default. Do not change the option, and click the‘Set Scripting Options’tab.

On the‘Set Scripting Options’screen, click the‘Advanced’button. When the‘Advanced Scripting Options’dialog box opens, do the following:
- Choose‘SQL Server 2008 R2’(or whatever SQL version you want) fromthe ‘Script for Server Version’drop-down list.
- Set‘Schema and data’as the‘Types of data to script’; this is crucial to generate data for each table.
- Under the‘Table/View Options’section, ensure that the‘Script Indexes,’ ‘Script Primary Keys,’and‘Script Triggers’are set to‘True.’

Click‘OK’. In the‘Set Scripting Options’屏幕出现,点击‘Save as script file’option and rename the script file if you want. Click‘Next’.

Note:Copy and paste the location where the script file gets stored, as you would need to open it in a lower SQL version. In our case, the location is path ‘C:\Users\charanjeet.kaur\Documents\scriptofBondDB.sql’.

Verify your selections from the‘Summary’page and then click‘Next’to generate the script.

Once the scripts get generated successfully, click‘Finish’to exit the‘Generate and Publish Scripts’wizard.

Before proceeding with the next step, copy and paste the script file to a shared network drive. From the network drive, you can copy the file to the D: drive on which the lower version of SQL Server is installed.

Step 2: Run the SQL Scripts in Lower Version of SQL Server

Here, we will connect to a lower version of SQL Server 2008 R2 (you may choose whatever version you want) and run the SQL script generated in Step 1. After connecting to the server instance, perform these steps:

Go to the‘File’menu, clickOpen > File.

On the‘Open File’dialog box, browse to the location where the SQL Script for version 2012 is saved. Next, select the script file and click‘Open.’

When the script opens, change the .mdf and .ldf files to the default location where the data and log files are saved in SQL Server 2008 R2.

After executing the above script successfully, right-click on‘Databases’and select‘Refresh.’The‘BondDB’will open in the lower version of SQL Server.

Downsides to this method

While the above approach to restore a newer SQL version to an older one might work, it has certain drawbacks:

If the higher version of the SQL Server database contains objects that are not supported in a lower version, you won't create those objects. In that case, you will need to review all the generated scripts and manually update the code in each script, which requires significant time and effort.
This is not a feasible approach for restoring large (complex) databases to an older SQL Server version.

What Else You Need to Know?

You can upgrade your lower version of SQL Server to the newer SQL version to restore the db from backup without any issues. Also, you can try restoring the backup on SQL Server 2012, export all the data, and then import it on a lower version of the SQL Server 2008R2 database.

When trying to restore a database backup, you might find that the backup is corrupted. In that case, you can use a professional SQL database technician likeStellar Repair for MS SQL technicianto extract data from the corrupted backup. Also, the technician software comprises tools to repair the database and restore all its components.