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 if you need to restore the backup to an older version. I recently came across such a scenario: restoring a SQL Server 2019 database to SQL Server 2008R2. With the attempt of restoring the database backup files, I get the following error message:
- 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 when restoring 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 an approach to downgrade a database from a higher version of SQL Server to a lower version of SQL Server. The solution has been tested and works with a small database size.
Solution
The error message in the problem message occurs because the SQL Server database files (*.mdf, *.ndf and *.ldf) and archives are not backward compatible. This is the reason why we cannot restore or attach a database created from a higher version of SQL Server to a lower version of SQL Server. However, there are a few 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 script generation wizard in SQL Server Management Studio
- Use SQL Server Integration Services
- Create custom scripts and BCPs
In this postt we will use the script generation wizard in SQL Server Management Studio.
Here are the basic steps to follow:
- Create the database schema and data script from the higher version of SQL Server using the script generation wizard in SSMS.
- 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 (host SRV02\SQLSERVER2012) to SQL Server 2008 R2 (host SRV01\SQLSERVER2008).
Steps to downgrade a SQL Server database using the SSMS script generation wizardPictures can be viewed from the original article.
Step 1
Make a script of the TEST_DB database schema of 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 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 press Next again 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 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 data for the table
- Under "Table/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 click Next to continue to the Summary page.
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 Generate and Publish Scripts Wizard: Generate and Publish Scripts.
Step 2
Connect to SQL Server 2008 R2 (SRV01\SQLSERVER2008) and then execute 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 click: open.
After loading the script, be sure to make changes to specify the correct location for the TEST_DB data and log files. Once you are done, run the script to create the TEST_DB database on instance SRV01\SQLServer2008.
Upon successful execution, /refresh/ the Database folder in Object Explorer to see that the TEST_DB database has been successfully downgraded.
Notes
There are a few things to know when using this approach.
- This solution creates a single SQL file that has the scripts to create the database objects and also the INSERT operators for the data in the tables.
- For large databases, the SQL file can get very large if you write both the schema and the data and can be difficult to load into an editor. You may also get a memory-related error message from the editor if the file is too large.
- For large databases, around 1 GB or more, if this approach doesn't work, then you should consider 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 just running the script, you should review the script to make sure everything looks correct, such as the database file path, 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 application before releasing it to users.
- Consider this downgrade option as a last resort to fall 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 to support the data needs.
- Be sure to check the number of rows and objects, and test your application before putting it into production.
Add Comment