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:
as shown in image below:
Step: 3: Execute the script. Now you will get your Sql server
database converted from sql server 2008R2 version to 2005 version.
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.
 |
| Click on image to enlarge |
Step 2: A window will appear as shown in figure below. Click on
Next.
 |
| 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.
 |
| 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.
 |
| 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.
 |
| Click on image to enlarge |
Step 6: A new window will appear as shown in fig. below. Click next
button.
 |
| 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.
 |
| 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:
 |
| Click on image to enlarge |
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."
7 comments
Click here for commentshello sir
Replyi 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
Hello mr. Bhupinder chauhan,
ReplyBelow 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
Thank you it was helpful
ReplyGreat work Mr. Lalit Raghuvanshi.
ReplyThanks Haceeb Javed for appreciating my work..keep reading for more useful updates like this..
Replyyour welcome Hend Adel..i am glad you liked my article..:)
ReplyIf 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..