How do I migrate my SQL server database to an earlier version?

Procédure

Why should I upgrade my SQL Server database to an earlier version?

The version of SQL Server currently used on our servers is version 2012. If you started your website locally with a more recent version, you may need to migrate your database to the 2012 version in order to import it into your hosting package from your Plesk control panel.

This documentation will explain how to do this.

Procedure for migrating your SQL Server database to an earlier version

The error message you may encounter when importing a SQL Server database occurs because the SQL Server database files (*.mdf, *.ndf and *.ldf) and backups are not backward compatible. Backward compatibility is the reason why we cannot restore or join 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 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 tip, we will use the Script Generation Wizard in SQL Server Management Studio.

1. Script the database schema and data in the higher version of SQL Server using the Script Generation 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.

For the purposes of this example, we will be migrating the "BDDLWS" database.

Step 1: Script the database to be migrated

Script the "LWS Trial" database schema on the instance above SQL Server 2012 (LWS-HOTLINERSQLEXPRESS) using the Generate Scripts in SSMS wizard.

In Object Explorer, connect to your SQL Server instance (LWS-HOTLINER), right-click on your database (BDDLWS), expand Tasks and choose"Generate Scripts...".

This launches the Generate and publish scripts wizard. Click Next to skip the introduction screen and go to the Choose objects page.

On the Choose objects page, choose the "Script the entire database and all database objects" option, then click Next to go to the "Define scripting options" page.

On the Define scripting options page, specify the location where you want to save the script file, then click the Advanced button .

In the Advanced Scripting Options dialog box ,

• set the script for the server version to SQL Server 2008 R2 (or the version of your choice)
• under Table / View Options, set Script Triggers, Script Indexes and Script Primary Keys to True

and set Data Types to Script for Schema and Data - this last option is essential as it generates data by table.

Once you have done this, click OK to close the Advanced Scripting Options dialog box and return to the Set Scripting Options page. From the Set Script Options page, click Next to continue to the Summary page.

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

Once the scripts have been successfully generated, click Finish to close the Generate and Publish Scripts wizard .

Step 2:
Connect to the SQL Server 2008 R2 instance (IITCUK \ SQLSERVER2008), then run the SQL scripts 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.

Edit the script to specify the correct location for the OUTLANDER database data and log files. Once you have done this, run the script to create the OUTLANDER database on the IITCUK \ SQLServer2008 instance.

After successful execution, refresh the database folder in the Object Explorer. As you can see from the following image, the OUTLANDER database has been successfully downgraded.

Remarks

There are a few points to bear in mind when using this approach.

• This solution creates a large SQL file containing the scripts for creating the database objects, as well as INSERT statements for the table data.
• For large databases, the SQL file can become very large if you write both the schema and the data, and it can be difficult to load into an editor. In addition, you may receive an editor memory error message if the file is too large.
• For large databases, around 1GB or more, if this approach doesn't work, consider using SSIS to migrate the database or create custom scripts to create a script for the objects and BCP for the data in each of the tables. You can use this script generation wizard to simply generate the schema without the data and use SSIS or BCP to export and import the data.
• This approach works from SQL Server 2017 to SQL Server 2005. Some of the scripting options may be slightly different in more recent versions, but the process remains the same.
• Before running the script, you should review it to make sure everything looks correct, such as the database file path, database options, etc.
• Similarly, if you use a new feature that doesn't exist in the lower version, SQL Server won't be able to create the objects and you'll need to check 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 some changes to the script for a more complex database.

Below is a list of all the script options. If you click on an item, the lower part of the screen gives you a short definition of the option.

Next steps

• To avoid this problem, 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 before distributing it to users.
• Consider this downgrade option as your last option to cancel an upgrade as the time and storage required can be very significant.
• With a very large database, make sure you have enough memory to meet the data requirements.
• Make sure you check the number of rows and objects and test your application before putting it into production.

Conclusion:

You are now ready to migrate your Sql Server database to an earlier version. Don't hesitate to contact our technical department via an incident ticket if you have any questions.