AccDox Shortener


How to downgrade a SQL Server database?

Original article:

Issue

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 ran into such a scenario: to restore a SQL Server 2019 database to SQL Server 2008R2. When attempting to restore the database backup files, the following error message is received:

- 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 similar error.

This error message is generated because SQL Server checks the database version on restore and does not allow you to restore a database from a newer version of SQL Server to an older version of SQL Server. Same with atach.

In this post, we will look at one approach to downgrade a database from a higher version of SQL Server to a lower version of SQL Server. The solution is tested and works with a small base size.

Answer

The error message in the problem message occurs because SQL Server database files (*.mdf, *.ndf, and *.ldf) and backups are not backwards compatible . This is the reason 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 the database from a higher version of SQL Server to a lower version of SQL Server.

These options include:

  • Use the Script Generation Wizard in SQL Server Management Studio
  • Use SQL Server Integration Services
  • Create custom scripts and BCPs

In this post, we'll use the Script Generation Wizard in SQL Server Management Studio.

Here are the main steps to follow:

  1. Scripting the database schema and higher SQL Server data using the SSMS Scripting Wizard.
  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 to downgrade 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 (eg SRV02\SQLSERVER2012) to SQL Server 2008 R2 (eg SRV01\SQLSERVER2008).

With photo material you can see from the original article.

Steps to downgrade a SQL Server database using the SSMS Script Generation Wizard

Stage 1

Let's script the SQL Server 2012 TEST_DB database schema (SRV02\SQLSERVER2012) using the SSMS Scripting Wizard.

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

This starts the wizard: Generate and Publish Scripts . Click Next to skip the Introduction screen and continue to the Choose Objects screen. From this screen select: "Script entire database and all database objects" and again press Next to get 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 the 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 key because this is what generates the table data
  • Under "T able/View Options ", set Script Triggers , Script Indexes and Script Primary Keys to True

When you're done, click OK to close the Advanced Scripting Options dialog box and return to the "Set Scripting Options" page. Here you can now press Next to proceed to the Summary wizard page.

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

After the scripts have been generated successfully, select the Finish button to close the Generate and Publish Scripts wizard.

Stage 2

Connect to SQL Server 2008 R2 (SRV01\SQLSERVER2008) and then run the SQL scripts that were generated in Step 1 to create the schema and data of the TEST_DB database in the low version.

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

After loading the script, be sure to make changes to specify the correct location for the TEST_DB database data and logs . Once done, run the script to create the TEST_ DB database on instance SRV01\SQLServer2008.

On successful completion, refresh /refresh/ the Database folder in Object Explorer to see that the TEST_DB database has been downgraded successfully.

Notes

There are a few things to be aware of when using this approach.

  • This solution creates a single SQL file that has the scripts to create the database objects and also INSERT statements for the data in the tables.
  • For large databases, the SQL file can become very large if you write both the schema and the data and can be difficult to load in an editor. You may also receive a memory-related error message from the editor if the file is too large.
  • For large databases, around 1GB or more, if this approach doesn't work, then you should look into using SSIS to migrate the database or create custom scripts to script the objects and BCP the data for each of the tables. You can use this script generation wizard to generate the schema without the data and 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 you just run the script, you should review the script to make sure everything looks correct, such as the path of the database files, database options, etc.
  • Additionally, if you use new functionality that does not exist in the lower version, SQL Server will not be able to create the objects and you will need to review the generated scripts and update the code accordingly.
  • For a very simple database, this approach should work fairly easily, but you may need to spend some time making some modifications to the script for a more complex database.

next steps

  • To avoid problems, always ensure that you perform a full database backup before upgrading SQL Server and the database to a higher version of SQL Server. Also, be sure to thoroughly test the app before releasing it to users.
  • Consider this downgrade option as the last option to roll back from an upgrade, as the time and storage required can be very large.
  • With a very large database, make sure you have enough storage space to support the data needs.
  • Don't forget to check the row and object count and test your application before you release it to production.


Comments

No Comments To Display

Add Comment

6 + 7 = ?