How to Restore SQL Server Database Backup to an Older Version of SQL Server?

MS SQL Server doesn't allow to downgrade a database from a higher version of server to a lower version. If you try to restore database backup created in a higher SQL Server version to an older version, you may encounter a “restore failed” error (see the below image).

As you can see, the error message clearly indicates that the restore of database failed because you’re trying to restore the backup file created in a newer version of SQL Server to an older version.

Though the database objects, like tables, stored procedures, views, etc., are compatible with 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.

However, if you need to restore SQL Server database backup to an older version, you can follow the workaround mentioned below.

Workaround to Restore Database Backup to an Older SQL Server Version

To restore database backup of a higher SQL Server version to an older version, you can generate scripts using the Generate Script wizard in the SSMS and then run these scripts in the lower SQL version. Let’s see how.

Step 1: Generate Scripts in Higher Version of SQL Server

First, you need to script the schema and data of the database using the Generate Script wizard in a higher version of SQL Server Management Studio (SSMS). The detailed steps are as follows:

• Open SQL Server Management Studio (SSMS), right-click a database, and click Tasks > Generate Scripts.

• On the 'Generate and Publish Scripts' window, click the 'Next' button.

• On the 'Choose Objects' screen, the 'Script entire database and all database objects' option is selected by default. Do not change the option. 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 the SQL version you want) from the 'Script for Server Version' dropdown 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' screen that appears, click 'Save as script file' option and rename the script file (if you want). Click 'Next'.
• Copy and paste the location where the script file gets stored. You need to open it in the lower SQL version in the next step
• Verify your selections from the 'Summary' page and then click 'Next' to generate the script.

• Once the script is generated successfully, click 'Finish' to exit the 'Generate and Publish Scripts' wizard.

• Now, copy and paste the script file to a shared network drive. From the network drive, you can copy the file to the drive on which the lower version of SQL Server is installed.

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

Now, you need to connect to a lower version of SQL Server. Here, we will connect to SQL Server 2008 R2. After connecting to the server instance, perform these steps:

• Go to the 'File' menu, click Open > File.

• On the 'Open File' dialog box, browse to the location where the SQL Script (created in above step) is saved. Next, select the script file and click 'Open.'

• When the script opens, change the location of .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 database will open in the lower version of SQL Server.

Drawbacks of the Above Method

While the above method can help you restore a SQL database to an older version, it has certain drawbacks, such as:

• If the higher version of the SQL Server database contains objects that are not supported in the 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 If your Backup File is Corrupted?

You may fail to restore the database backup, if the backup (.bak) file is corrupted. In that case, you can use Stellar Repair for MS SQL Technician - a professional SQL repair software that can extract data from corrupted SQL database backup (.bak) files. It saves extracted data in a new database, live database, or various other formats. The software can also recover data from compressed SQL backup files. The tool supports all SQL backup types, including Full Backup, Differential Backup, and Transaction Log Backup. In addition, it can repair corrupted SQL database files of any size and recover all the objects with complete integrity. The software is compatible with MS SQL 2022, 2019, and lower versions.

Conclusion

It is not possible to directly restore a SQL Server database backup to an older version of SQL Server. However, you can script the schema and data of the database and run the script in the lower version of the SQL Server, as discussed in this article. If your backup file is unreadable or corrupted, then you can use Stellar Repair for MS SQL Technician. This advanced tool can extract data from corrupt SQL database or backup files with complete integrity.