How to convert/downgrade SQL Server 2012,2008 database to SQL Server 2005 or lower version

Introduction: In previous articles i explained How to Backup and restore sql server database and How to create Sql server database script and Create database from that script and How to take Sql server database backup and How to take automatic backup of Sql server Database .
Now In this article I will explain how to convert/downgrade SQL server 2008 R2 database to SQL server 2008, SQL server 2005 or  SQL server 2000 version. Similarly other version like SQL Server 2012 can also be converted to lower versions.

Implementation: Let's check it practically. Follow the steps mentioned below:

Step 1:First we will create the script of the sql server 2008R2 database.Note: Scripted database will have all the data also. So open/launch SQL Server Management Studio 2008R2. In Object Explorer, right click on the database that you want to convert. Select  Tasks - > Generate Scripts as shown in fig below.

Convert sql server 2008 database to sql server 2005
Click on image to enlarge

Step 2: A window will appear as shown in figure below. Click on Next.

Convert sql server 2008 database to sql server 2005
Click on image to enlarge
Step 3: A new window will appear as shown in figure below. Select the option Script entire database and all database objects. Click on Next button.

Convert sql server 2008 database to sql server 2005
Click on image to enlarge
Step 4: A new window will appear. Browse and give the path to the file name where you want to save the Database script. E.g. In our case “D:\Lalit\MyDataBaseScript.sql”. Now click on the Advance button as shown in figure below.

Convert sql server 2008 database to sql server 2005
Click on image to enlarge
Step 5: A new window will appear. Scroll down the window and in the option Script for Sql version select Sql Server 2005 from drop down and for Types of data to script select Schema and data from drop down as shown in fig below. Note:  You can also convert your sql server 2008 R2 database to Sql server 2000, Sql server 2008 also by selecting the appropriate version from the dropdown in Script for Sql version option. Click on Ok button and then Next button.

Convert sql server 2008 database to sql server 2005
Click on image to enlarge
Step 6: A new window will appear as shown in fig. below. Click next button.

Convert sql server 2008 database to sql server 2005
Click on image to enlarge
Step 7: A new window will appear as shown in fig below showing the status of saving or publishing scripts. Wait till it completes and after completion click on finish button. Congrats you have converted your sql server 2008R2 database to Sql server2005 database script.

Convert sql server 2008 database to sql server 2005
Click on image to enlarge

 Now to create SQL SERVER 2005 database from the generated database script follow the following steps:

Step 1: Open/launch sql server management studio 2005 and drag the generated script onto the Query editor window of sql server or simply open the script in sql server management studio 2005.

Step: 2 Just change the path from this C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDataBase.mdf
to this C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\MyDataBase.mdf
And  from this C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDataBase_log.ldf  to this
C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\MyDataBase_log.ldf

as shown in image below:

convert sql server 2012,2008r2,2008 to 2005,2000 lower versions
Click on image to enlarge
Step: 3: Execute the script. Now you will get your Sql server database converted from sql server 2008R2 version to 2005 version.

Now over to you:

"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates."
Previous
Next Post »

7 comments

Click here for comments
Unknown
admin
August 08, 2013 ×

hello sir
i am beginner in sql server
please help me on this select query

i want show data like this
column1
1
2
3
4
5
6
7
8
9
10

now i want select query to show data like this
1 2 3 4 5 6 7 8 9 10

Reply
avatar
August 08, 2013 × This comment has been removed by the author.
avatar
August 08, 2013 ×

Hello mr. Bhupinder chauhan,
Below is the queries as per your requirement


DECLARE @Str varchar(max)
SELECT @Str=COALESCE(@Str,'') + CAST(YourColumn as varchar(100)) + ' '
FROM YourTable
SELECT @Str
It will return all the records of the column in a row separated by a single space


DECLARE @Str varchar(max)
SELECT @Str=COALESCE(@Str,'') + CAST(YourColumn as varchar(100)) + ','
FROM YourTable
SELECT @Str

It will return all the records of the column in a row separated by a comma

Reply
avatar
Hend Adel
admin
December 11, 2013 ×

Thank you it was helpful

Reply
avatar
Unknown
admin
December 11, 2013 ×

Great work Mr. Lalit Raghuvanshi.

Reply
avatar
December 11, 2013 ×

Thanks Haceeb Javed for appreciating my work..keep reading for more useful updates like this..

Reply
avatar
December 11, 2013 ×

your welcome Hend Adel..i am glad you liked my article..:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..