Original article:

• https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/
• https://accdox.com/h1d08

Problem

You have backup databases from a higher version of SQL Server, and for some reason, you need to restore the backup to an older version. I recently encountered such a scenario: restoring a database from SQL Server 2019 to SQL Server 2008 R2. When attempting to restore the database backup files, the following error message appears:

- Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'DatabaseName' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

or a similar error.

This error message is generated because SQL Server checks the database version during restoration and does not allow you to restore a database from a newer version of SQL Server to an older version of SQL Server. The same applies to the ATTACH command.

In this post, we’ll explore one approach for downgrading a database from a higher version of SQL Server to a lower version of SQL Server. The solution has been tested and works with small databases.

Solution

The error message in the issue report occurs because the SQL Server database files (*.mdf, *.ndf, and *.ldf) and archives are not backward compatible. This is why we cannot restore or attach a database created by a higher version of SQL Server to a lower version of SQL Server. However, there are several options that can help us downgrade a database from a higher version of SQL Server to a lower version of SQL Server.

These options include:

• Use the Scripting Wizard in SQL Server Management Studio
• Use SQL Server Integration Services
• Create custom scripts and use BCP

In this post, we’ll use the script generator wizard in SQL Server Management Studio.

Here are the main steps to follow:

1. Generate scripts for the database schema and data from a higher version of SQL Server using the script generator in SSMS.
2. Connect to the lower version of SQL Server and run the SQL scripts that were generated in the previous step.

In the next section, I will describe the steps for downgrading a SQL Server 2012 database to a SQL Server 2008 R2 database.

Note: For demonstration purposes, we will downgrade the TEST_DB database, hosted on SQL Server 2012 (for example, SRV02\SQLSERVER2012) to SQL Server 2008 R2 (for example, SRV01\SQLSERVER2008).

You can view screenshots from the original article.

Steps to downgrade a SQL Server database using the script generation wizard in SSMS

Step 1

Let’s create a script for the schema of the TEST_DB database on SQL Server 2012 (SRV02\SQLSERVER2012) using the Script Generation Wizard in SSMS.

In Object Explorer, connect to SRV02\SQLSERVER2012, right-click the TEST_DB database, expand Tasks, and select "Generate Scripts...".

This launches the Generate and Publish Scripts wizard. Click Next to skip the Introduction screen and proceed to the Choose Objects screen. From this screen, select: "Script entire database and all database objects" and click Next again to proceed to: "Set Scripting Options".

From this screen, specify the location where you want to save the script file, and then click the Advanced button.

In the Advanced Scripting Options dialog box, select:

• Set Script for Server Version to SQL Server 2008 R2 (or the version you want)
• Set Types of data to script to Schema and Data—this option is crucial because it generates the table data
• Under "Table/View Options", set Script Triggers, Script Indexes, and Script Primary Keys to True

Once you’re ready, click OK to close the Advanced Scripting Options dialog box and return to the "Set Scripting Options" page. Here, you can now click Next to proceed to the Summary page of the wizard.

After reviewing your selections on the Summary page, click Next to generate the scripts.

Once the scripts have been successfully generated, select the Finish button to close the wizard for generating and publishing scripts: Generate and Publish Scripts.

Step 2

Connect to SQL Server 2008 R2 (SRV01\SQLSERVER2008) and then execute the SQL scripts generated in Step 1 to create the schema and data for the TEST_DB database in the legacy version.

In Object Explorer, connect to SRV01\SQLServer2008 (the older version of the SQL Server), then in SQL Server Management Studio, select the script we generated in Step 1 from: File/Open/File ..., select the file, and click Open.

After loading the script, be sure to make changes to specify the correct location for the data and log files of the TEST_DB. Once you are ready, run the script to create the TEST_DB database on the SRV01\SQLServer2008 instance.

If the execution is successful, refresh the Database folder in Object Explorer to verify that the TEST_DB database has been successfully downgraded.

Notes

There are a few things you should know when using this approach.

• This solution generates a single SQL file that contains the scripts for creating the database objects, as well as INSERT statements for the data in the tables.
• For large databases, the SQL file can become very large if you include both the schema and the data, and it may be difficult to load into an editor. In addition, you may receive a memory-related error message from the editor if the file is too large.
• For large databases, around 1 GB or larger, if this approach does not work, you should consider using SSIS to migrate the database or creating custom scripts for the object schemas and BCP for the data for each of the tables. You can use this script generator to generate the schema without the data and then use SSIS or BCP to export and import the data.
• This approach works for SQL Server 2005 through SQL Server 2019. Some of the scripting options may be slightly different in newer versions, but the process remains the same.
• Before simply running the script, you should review it to ensure that everything looks correct, such as the database file paths, database options, and so on.
• Additionally, if you’re using new functionality that doesn’t exist in an older version, SQL Server won’t be able to create the objects, and you’ll need to review the generated scripts and update the code accordingly.
• For a very simple database, this approach should work quite easily, but you may need to spend some time making modifications to the script for a more complex database.

Next Steps

• To avoid problems, always make sure you perform a full database backup before upgrading SQL Server and the database to a higher version of SQL Server. Also, be sure to test the application thoroughly before releasing it to users.
• Consider this downgrade option as a last resort for rolling back from an upgrade, as the time and storage space required can be significant.
• If you have a very large database, make sure you have enough storage space to support your data requirements.
• Be sure to check the number of rows and objects, and test your application before deploying it to production.