Downgrade SQL Database 2012 to 2008 Or Earlier Versions

Problem:
Sometimes peoples have requirements; they need to transfer the database from newer versions to earlier versions due to client’s necessity. As I said, you have a database on SQL Server 2012, but your customers have 2008r2 or earlier versions. Migrating the database from older SQL server to the newer version is pretty much easy. All you need a complete backup and restore it to the latest version or release as SQL 2012 have backward compatibilities. However, Downgrading SQL Database 2012 to 2008 Or Earlier Versions is not that simple. In order to overcome, these problems follow the steps below.

Solutions:
To the best of my information, I would recommend you two simple ways to complete this task.
• Generate Complete Scripts for both Schema and data
• Generate Script for Schema & Use Import / Export utility.

Some folks are thinking why am I suggesting two methods, what are we supposed to do? So, don’t worry. It depends on best practices and size of your database.

Generating Database Scripts:

This method is very useful and attractive for database developers to transferring their database structure or schema objects like tables, views, stored procedures, functions. However, some of you might know that you can transfer data as well. Let’s start.

Login to your SQL Server Instance where you have hosted your database. Right click on your database that you want to transfer, go to tasks > generate scripts as you can see in the above picture.

Once you can create script, you will be redirected to this wizard. Then press ‘Next ‘ button to proceed further. There are four steps to complete this wizard.

 

On this screen, you will have the option to choose objects. By default, all objects of the entire database will be selected. Although you can select your desire objects such as tables, views and etc. if you do not have an idea then go with the default, like I did on this screen and ‘next ‘.

Next Screen is “set scripting options”. Now don’t go with next, this time you have to navigate to Advance options because we have to change some parameters that are necessary for transferring data from newer to lower versions. So, go to advanced and here have to the next screen.

In advance screen, the first parameter, which you need to set, is ” Script for Server Version”, as you can see, choose whatever version which fulfils your requirements. In my case, I’ll select SQL Server 2008 R2.

Second parameter, which you need to select, is “Types of data to script”. There are three option:
• Data Only
• Schema and data
• Schema Only
Let me clarify all three types, benefits and drawbacks.

Data Only:

Data only is an excellent option given by SQL server. Quite attractive if you are moving your data from one place to another via SQL file. This method will extract all your data into .sql file command. The only drawback is that the .sql file becomes heavier with respect to the data. If you are moving small size database under couple of GBs then, it will be a good option to transfer. However, if you are dealing with more than this then, it will be chaos for you.

Schema and data:

Schema and data just mean you can script both schema and data in single .sql file. Condition applies as I said earlier if you are dealing with big data like more than couple of Gbs then it will take time and space to convert this database structure and data into .sql file.

Schema Only:

As describe Schema only. You can script all your database structure means tables, views, stored procedures, functions and etc. into SQL file.
I would recommend you to script schema and data only if you are dealing with the couple of Gbs. You can also script Schema only and data only into two different files . It will allow you to maintain your DDL and DML separately. As of now I will go with Schema and data then press OK.

After couple of next, you will be here at final stage. As you can see your entire database schema with structures have successfully extracted into SQL file. Now our half work has completed. We have exported the database with data. Now let start to execute this SQL file on SQL Server 2008 R2.