Problem
Recently, we had a request to restore a SQL Server 2012 database to SQL Server 2008R2 i.e., downgrade SQL Server. We tried a backup and restore of the database, but we received the following error. The message indicates that the SQL Server database backup compatibility is not possible with an older version:
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.This error message is generated because SQL Server checks the version of the database when restoring. SQL Server does not allow a database restore from a newer version to an older version. This would be a downgrade of the SQL Server database version, which is not directly possible. This is the same issue when attaching a database from a higher version of SQL Server to a lower version.
In this tip, we outline one approach to downgrade the database from a higher version of SQL Server to a lower version.
Solution
The error occurs because SQL Server database files (*.mdf, *.ndf and *.ldf) and backups are not backward compatible. Backward compatibility is why we cannot restore or attach a database created in a higher version of SQL Server to a lower version. However, there are a few options that can help us to downgrade the database from a higher version of SQL Server to a lower version SQL Server.
These options include:
- Use the Generate Scripts Wizard in SQL Server Management Studio
- Use SQL Server Integration Services
- Create Custom Scripting and BCP
In this tip we will use the Generate Scripts Wizard in SQL Server Management Studio.
Here are the basic steps we need to follow:
- Script the database schema and data from the higher version of SQL Server by using the Generate Scripts Wizard in SSMS.
- Connect to the lower version of SQL Server, and run the SQL scripts that were generated in the previous step, to create the database schema and data.
Next, I demonstrate the steps for downgrading a SQL Server 2012 database to SQL Server 2008 R2 database.
Note: I will downgrade the OUTLANDER database on SQL Server 2012 (IITCUK\DEV01) to SQL Server 2008 R2 instance (IITCUK\SQLSERVER2008).
Steps to Downgrade a SQL Server Database Using SSMS Generate Scripts Wizard
Step 1
Script the schema of the OUTLANDER database on the SQL Server 2012 instance (IITCUK\DEV01) using the Generate Scripts wizard in SSMS.
In Object Explorer connect to IITCUK\DEV01, right-click on the OUTLANDER database, expand Tasks and choose “Generate Scripts…”.
This launches Generate and Publish Scripts wizard. Click Next, to skip the Introduction screen and proceed to the Choose Objects page.
On the Choose Objects page, choose option “Script entire database and all database objects”, and then click Next to proceed to “Set Scripting Options” page.
On the Set Scripting Options page, specify the location where you want to save the script file, and then click the Advanced button.
In the Advanced Scripting Options dialog box,
- set Script for Server Version to SQL Server 2008 R2 (or whatever version you want)
- under the Table/View Options, set Script Triggers, Script Indexes and Script Primary Keys to True
- and set Types of data to script to Schema and Data – this last option is key because this is what generates the data per table
Once done, click OK, to close the Advanced Scripting Options dialog box and return to Set Scripting Options page. In Set Scripting Options page, 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 are generated successfully, choose the Finish button to close the Generate and Publish Scripts wizard.
Step 2
Connect to the SQL Server 2008 R2 instance (IITCUK\SQLSERVER2008), and then run the SQL scripts that were generated in Step 1, to create the OUTLANDER database schema and data.
In Object Explorer connect to IITCUK\SQLServer2008, then in SQL Server Management Studio, open the SQL Server script you saved in Step 1.
Modify the script, to specify the correct location for the OUTLANDER database data and log files. Once done, run the script to create the OUTLANDER database on IITCUK\SQLServer2008 instance.
Upon successful execution, refresh the Database folder in Object Explorer. As you can see in the following image, the OUTLANDER database has been successfully downgraded.
Notes
There are a few things to be aware of when using this approach.
- This solution creates one large SQL file that has the scripts to create the database objects and also INSERT statements for the data in the tables.
- For a large databases, the SQL file can get very large if you script out both the schema and the data and could be hard to load into an editor. Also, you may get a memory related error message from the editor if the file is too big.
- For large databases, around 1GB or more, if this approach does not work, then you should look at using SSIS to migrate the database or create custom scripts to script out the objects and BCP out the data for each of the tables. You can use this Generate Scripts wizard to just 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 might be a bit different in newer versions, but the process is still the same.
- Before just executing the script, you should review the script to make sure everything looks correct such as the path of the database files, database options, etc.
- Also if you are using new functionality that does not exist in the lower version, SQL Server won’t be able to create the objects and you will need to review the scripts that were generated and update the code accordingly.
- For a very simple database this approach should work pretty easliy, but you might need to spend some time making some modifications to the script for a more complex database.
- Below is a list of all of the scripting options. If you click on an item, the bottom part of the screen gives you a short definition of the option.
Next Steps
- To avoid this issue, always make sure that you perform a full backup of the database before you upgrade SQL Server and database to a higher version of SQL Server. In addition, be sure to thoroughly test the application prior to releasing the application to the users.
- Consider this downgrade option as your last option to rollback from an upgrade because the time and storage needed can be very large.
- With a very large database be sure you have sufficient storage to support the data needs.
- Be sure to verify row and object counts as well as test your application before releasing to production.
- Additional Resources:
