How to Downgrade SQL Server Database Using SSIS Import Export Wizard

How to Downgrade SQL Server Database Using SSIS Import Export Wizard:

Introduction

In my previous article I had explained the reasons Why an SQL Server Database from a higher version cannot be restored onto a lower version? However, in this article we will go through the steps to downgrade an SQL Server 2008 R2 database to SQL Server 2008 using SQL Server Import and Export Wizard which internally leverages SQL Server Integration Services. The steps mentioned in this article are also applicable to downgrade database from SQL Server 2008 R2 to SQL Server 2005 or SQL Server 2000.

1. Connect to SQL Server Database Instance Using SQL Server Management Studio and then expand Databases Node.

2. Right click AdventureWorksLT2008R2 database, expand Tasks and choose Export Data option from the drop down menu to open up SQL Server Import and Export Wizard as shown in the image below.

 

3. In SQL Server Import and Export Wizard click Next as shown in the image below to continue with the wizard.

  

4. In Choose a Data Source wizard screen, you need to specify the Data Source as SQL Server Native Client 10.0 and provide the Source Server Name, Authentication information and also choose the Source Database as AdventureWorksLT2008R2 from the drop down list as shown in the image below. Click Next to continue with the wizard.

 

5. In Choose a Destination wizard screen, you need to specify the Destination as SQL Server Native Client 10.0 and provide the Destination Server Name, Authentication information and also you need to specify the Destination Database. As shown in the image below click New…. button under database to Create a Destination Database.

   

6. In Create Database Popup Window you need to specify the Destination Database Name,Initial Size for Data and Log files, Growth Parameters for Data and Log files as shown in the image below. Once you click OK you will return to Choose a Destination wizard screen. Click Nextto continue with the wizard.

    

7. In Specify Table Copy or Query wizard screen you need to choose the first option which is Copy data from one or more tables or views as shown in the image below and click Next to continuw with the wizard.

 

8. In Select Source Table and Views wizard screen, as shown in the below image choose all the tables and Click Next to continue with the wizard screen.

   

9. In Save and Run Package wizard screen you will be given an option to either run the package immediately or else to Save SSIS Package on to SQL Server or to a File System. Choose the option as Run Immediately and click Next to continue with the wizard.

    

10. In Complete the Wizard screen you will be able to see a quick summary of options which you have selected so far. Click Finish to start the SSIS package execution to downgrade the SQL Server database from higher version to lower version.

 

11. In the image below you could see that the SSIS package executed successfully and it transferred the data from SQL Server 2008 R2 to SQL Server 2008.

   

12. In the image below you could see that using SQL Server Import and Export wizard we were able to successfully downgrade the database from SQL Server 2008 R2 to SQL Server 2008.

   

Conclusion

In this article you have seen how to downgrade an SQL Server 2008 R2 database to SQL Server 2008 database. The steps mentioned in this article is also applicable to downgrade a SQL Server 2008 R2 database to SQL Server 2005 or SQL Server 2000.