Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 10 June 2015

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.
Open SQL Server 2008 Import Export Wizard 

3. In SQL Server Import and Export Wizard click Next as shown in the image below to continue with the wizard.
SQL Server 2008 Import Export 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.
SQL Server 2008 Import Export Wizard Choose a Data Source Screen 

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.
SQL Server 2008 Import Export Wizard Choose a Destination Screen   
6. In Create Database Popup Window you need to specify the Destination Database Name,Initial Size for Data and Log filesGrowth 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.
Create Database Using SQL Server 2008 Import Export 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.
Copy data from one or more table or views screen in SQL Server 2008 Import Export 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.
Select Source Tables and Views in SQL Server 2008 Import Export Wizard   
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.
Save and Run SSIS Package Using SQL Server 2008 Import Export 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.
Complete the Wizard Screen in SQL Server 2008 Import Export Wizard 

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.
SQL Server 2008 Import Export Wizard Successfull Package Screen   
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.
Downgrade SQL Server 2008 R2 Database to SQL Server 2008 Using SQL Server 2008 Import Export Wizard   

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.