I have to downgrade a eCommerce database from Sql Server 2005 to Sql Server 2000 due to hosting server's support compatibility. Almost all features in the original (2005) version are available in 2000.
Any recommendations as on an easy conversion? Thanks a lot.
It's not possible to easily/directly convert a SQL Server 2005 database to SQL Server 2000. I learned this the very hard way. My suggestion would be to find a new host.
I have the same issue, in that I developed an ASP.Net 2.0 application using SQL Server Express. Now I discover that my hoster, GoDaddy, offers only SQL Server 2K (plus Access and MySql). Although the DB is fairly basic, I don't want to manually recreate it.
When I tried running a script of the DB (script created in SQL Server Express) in SQL Server 2K Query Analyzer, it failed with many errors. What a pain.
It is actually much easier than you think. Just use Sql Server Express to generate database script, with specification of compatibility set to "Sql Server 2000". Run the script in Sql Server 2000, you're all set.
I just tried it and it seems to work ... at least the tables and triggers appear in Enterprise Mgr. I need to take two steps before I'm home free:
(1) Do some basic Q/A attaching my app to SQL Server 2K (may have some "Personalization" DB issues).
(2) Upload it to my hoster (GoDaddy) and make sure it runs properly.
Thank you so much for pointing out this feature in SQL Server Express. I have spent many hours today trying to manually edit a script file, generated in SQL Server Express, in a futile attempt to get it to run in SQL Server 2K Query Analyzer. Thanks again;
I really appreciate your help!
How to Downgrade a Database from SQL Server 2005 to SQL Server 2000
As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn’t knew that and after a while from
the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!
At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition.
Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow
for this action.
Before beginning I must assume, firstly that the user, who will attempt such thing, has
a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created
at the SQL Server 2000 environment as well.
Step 1 Generating Scripts for the Database
Elements and Structures
1)Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).
2)At the pop-up Dialog Box click at the
Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.
3)Set the following Elements to the following Values
a.Script Collation , set to TRUE
b.Script Database Create, set to TRUE
c.Script of SQL Version, set to SQL SERVER 2000
d.Script foreign keys, set to FALSE
e.Script Triggers, set to FALSE
Then Hit the Next button
4)Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
5)Click Finish
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts
that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.
Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the
Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.
Step2
Moving the data from 2005 to 2000
1)After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).
2)From the pop-up Dialog Box, select the Source Db and Click at the Next Button.
3)At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.
4)A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog
Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be
moved.
CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I
have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected
Elements is smaller than 12 no problem seemed to appear.
Step 3 Generating Scripts for the Database
Foreign Keys and Triggers
Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should
be followed is the one stated next:
1)Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.
2)Using the pop-up Dialog Box make sure that the check box
Script All Objects in the selected Databases is not enabled and hit the Next Button.
3)Set all the Elements on the List to a False Value except the ones that follow:
a.Include IF NOT EXISTS , set to TRUE
b.Script Owner, set to TRUE
c.Script of SQL Version, set to SQL SERVER 2000
d.Script foreign keys, set to TRUE
e.Script Triggers, set to TRUE
Then Hit the Next button
4)After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.
5)At the screen that follows hit the Select All button and the Next.
6)Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
7)Click Finish Button.
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts
that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.
After these steps the database should be fully functional under the SQL Server 2000 edition.
Marked as answer by tmorton on Dec 04, 2007 07:01 PM
Here's how to upload a local Sql Server Express .mdf database, complete with the
table data, using Visual Web Developer 2008 Express, to a
Sql 2000 remote account!
1. Right-click the .mdf file in Databse Explorer in VWD. Choose Publish to Provider...
2. Click Next; Select your db and click Next, and Next again to simply put the resulting .sql file whereever Windows wants it.
3. Select the Sql Server 2000 option from the middle dropdown menu. This is the one which does the trick!
4. Follow the instructions to finish the guide.
5. Open Sql Management Studio Express (which you must have downloaded). Connect to the remote host's db account and find your "db". Right-click and choose New Query...
6. Find your .sql file in Windows Explorer. Right-click and choose Open with Sql Management Studio Express.
7. Copy the contents of your file into the empty "New Query... file". Click Execute or hit F5. Refresh the db pane of Sql Management Studio Express to view your changes.
Yubo
Member
145 Points
37 Posts
Convert Sql Server 2005 to Sql Server 2000
Jun 30, 2006 02:47 PM|LINK
I have to downgrade a eCommerce database from Sql Server 2005 to Sql Server 2000 due to hosting server's support compatibility. Almost all features in the original (2005) version are available in 2000.
Any recommendations as on an easy conversion? Thanks a lot.
-Yubo
tmorton
All-Star
56327 Points
9683 Posts
ASPInsiders
Moderator
Re: Convert Sql Server 2005 to Sql Server 2000
Jul 01, 2006 03:35 AM|LINK
ASP.NET/IIS.NET Website Manager, Neudesic
melvinsb1
Member
80 Points
16 Posts
Re: Convert Sql Server 2005 to Sql Server 2000
Jul 18, 2006 07:07 PM|LINK
Yubo
Member
145 Points
37 Posts
Re: Convert Sql Server 2005 to Sql Server 2000
Jul 18, 2006 07:49 PM|LINK
It is actually much easier than you think. Just use Sql Server Express to generate database script, with specification of compatibility set to "Sql Server 2000". Run the script in Sql Server 2000, you're all set.
Yubo
melvinsb1
Member
80 Points
16 Posts
Re: Convert Sql Server 2005 to Sql Server 2000
Jul 18, 2006 08:39 PM|LINK
I just tried it and it seems to work ... at least the tables and triggers appear in Enterprise Mgr. I need to take two steps before I'm home free:
(1) Do some basic Q/A attaching my app to SQL Server 2K (may have some "Personalization" DB issues).
(2) Upload it to my hoster (GoDaddy) and make sure it runs properly.
Thank you so much for pointing out this feature in SQL Server Express. I have spent many hours today trying to manually edit a script file, generated in SQL Server Express, in a futile attempt to get it to run in SQL Server 2K Query Analyzer. Thanks again; I really appreciate your help!
champ1979
Member
167 Points
66 Posts
Re: Convert Sql Server 2005 to Sql Server 2000
Jul 25, 2006 04:33 PM|LINK
Any other ideas? Are there any online sql conversion or validation tools that migt be useful?
Thanks
Yubo
Member
145 Points
37 Posts
Re: Convert Sql Server 2005 to Sql Server 2000
Jul 25, 2006 05:16 PM|LINK
What issue are you having when running DB script in SS2K? Please list some details.
I don't know of any online sql conversion/validation tool.
-Yubo
sunny_popali
Member
37 Points
48 Posts
Re: Convert Sql Server 2005 to Sql Server 2000
Nov 29, 2007 11:28 AM|LINK
May be this will help !
How to Downgrade a Database from SQL Server 2005 to SQL Server 2000
As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn’t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!
At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.
Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.
Step 1 Generating Scripts for the Database Elements and Structures
1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).
2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.
3) Set the following Elements to the following Values
a. Script Collation , set to TRUE
b. Script Database Create, set to TRUE
c. Script of SQL Version, set to SQL SERVER 2000
d. Script foreign keys, set to FALSE
e. Script Triggers, set to FALSE
Then Hit the Next button
4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
5) Click Finish
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.
Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.
Step2 Moving the data from 2005 to 2000
1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).
2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.
3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.
4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.
CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.
Step 3 Generating Scripts for the Database Foreign Keys and Triggers
Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:
1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.
2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.
3) Set all the Elements on the List to a False Value except the ones that follow:
a. Include IF NOT EXISTS , set to TRUE
b. Script Owner, set to TRUE
c. Script of SQL Version, set to SQL SERVER 2000
d. Script foreign keys, set to TRUE
e. Script Triggers, set to TRUE
Then Hit the Next button
4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.
5) At the screen that follows hit the Select All button and the Next.
6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
7) Click Finish Button.
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.
After these steps the database should be fully functional under the SQL Server 2000 edition.
inesense
Member
6 Points
4 Posts
Re: Convert Sql Server 2005 to Sql Server 2000
Dec 03, 2007 01:21 AM|LINK
Hi,
This is what I want. Thank for the suggestion. I will try it.
pettrer
Participant
970 Points
469 Posts
Re: Convert Sql Server 2005 to Sql Server 2000
Dec 18, 2007 12:38 PM|LINK
Hi,
Here's how to upload a local Sql Server Express .mdf database, complete with the table data, using Visual Web Developer 2008 Express, to a Sql 2000 remote account!
1. Right-click the .mdf file in Databse Explorer in VWD. Choose Publish to Provider...
2. Click Next; Select your db and click Next, and Next again to simply put the resulting .sql file whereever Windows wants it.
3. Select the Sql Server 2000 option from the middle dropdown menu. This is the one which does the trick!
4. Follow the instructions to finish the guide.
5. Open Sql Management Studio Express (which you must have downloaded). Connect to the remote host's db account and find your "db". Right-click and choose New Query...
6. Find your .sql file in Windows Explorer. Right-click and choose Open with Sql Management Studio Express.
7. Copy the contents of your file into the empty "New Query... file". Click Execute or hit F5. Refresh the db pane of Sql Management Studio Express to view your changes.
That's it!
Pettrer, Sweden