Downgrade version the database of SQL Server

Downgrade version the database of SQL Server
Downgrade version the database of SQL Server

Downgrade version of the database of SQL Server

Do we need to downgrade the version of SQL Server?

Here we have the steps to follow to make the downgrade.

Solution

Script the database schema and data from the higher version of SQL Server by using the Generate Scripts Wizard in SSMS

Step 1

Script the schema of the OUTLANDER database on the SQL Server 2012 instance (IODOCS\DEV) using the Generate Scripts wizard in SSMS.

In Object Explorer, connect to IODOCS\DEV, right-click on the OUTLANDER database, expand Tasks and choose “Generate Scripts…”

Downgrade version of the database of SQL Server Step 1
Downgrade version of the database of SQL Server Step 1

These 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 the option “Script entire database and all database objects,” and then click Next to proceed to the “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 generates the data per table.

Once done, click OK to close the Advanced Scripting Options dialog box and return to the Set Scripting Options page. On the Set Scripting Options page, click Next to continue to the Summary page.

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

Once 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 (IODOCS\SQLSERVER2008), and 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.

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 IODOCS\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.