Have you ever encountered the need to restore or attach a SQL Server database from a higher version to a lower version, only to be met with an error message stating that the downgrade is not supported? This can be a frustrating situation, especially when you need to migrate your database to a different server or environment. In this article, we will explore a solution to this problem by using the Generate Scripts Wizard in SQL Server Management Studio.

The Problem

When attempting to restore or attach a database from a higher version of SQL Server to a lower version, you may encounter 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.

This error occurs because SQL Server checks the version of the database when restoring or attaching, and does not allow you to perform a downgrade. This can be a major roadblock when you need to move your database to a lower version of SQL Server.

The Solution

Fortunately, there are several options available to downgrade a SQL Server database from a higher version to a lower version. In this article, we will focus on using the Generate Scripts Wizard in SQL Server Management Studio. Here are the basic steps:

1. Script the database schema and data from the higher version of SQL Server using the Generate Scripts Wizard in SSMS.
2. Connect to the lower version of SQL Server and run the SQL scripts generated in the previous step to create the database schema and data.

Step 1: Scripting the Database

To begin, launch SQL Server Management Studio and connect to the higher version of SQL Server. Follow these steps:

1. In Object Explorer, right-click on the database you want to downgrade and choose “Tasks” > “Generate Scripts…”.
2. In the Generate and Publish Scripts wizard, choose the option to script the entire database and all database objects.
3. Specify the location where you want to save the script file, and then click the “Advanced” button.
4. In the Advanced Scripting Options dialog box, set the “Script for Server Version” to the version of SQL Server you want to downgrade to.
5. Set the options to script triggers, indexes, and primary keys.
6. Make sure to select the option to script both the schema and data.
7. Click “OK” to close the Advanced Scripting Options dialog box.
8. Review your selections and click “Next” to generate the scripts.
9. Once the scripts are generated successfully, click “Finish” to close the Generate and Publish Scripts wizard.

Step 2: Running the Scripts

Now that you have the scripts, you can proceed to the next step of downgrading the database. Follow these steps:

1. Connect to the lower version of SQL Server using SQL Server Management Studio.
2. Open the SQL script you saved in Step 1.
3. Modify the script to specify the correct location for the database data and log files.
4. Run the script to create the database on the lower version of SQL Server.
5. Refresh the Database folder in Object Explorer to verify that the database has been successfully downgraded.

Conclusion

By using the Generate Scripts Wizard in SQL Server Management Studio, you can successfully downgrade a SQL Server database from a higher version to a lower version. This approach provides a reliable solution when you need to migrate your database to a different server or environment. However, it is important to note that this method may not be suitable for very large databases or databases with complex structures. In such cases, alternative methods like using SQL Server Integration Services or creating custom scripts may be more appropriate. Regardless of the method chosen, it is crucial to review and modify the scripts as necessary to ensure a successful downgrade.