Convert Sql Server 2005 to Sql Server 2000

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

0
Yubo
6/30/2006 2:47:14 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

11 Replies
693 Views

Similar Articles

[PageSpeed] 17

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.



Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
7/1/2006 3:35:54 AM
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.
0
melvinsb1
7/18/2006 7:07:30 PM

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

0
Yubo
7/18/2006 7:49:29 PM

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!

0
melvinsb1
7/18/2006 8:39:48 PM
I had the same issue...tried what was suggested (changed the "Script for Server Version" to SQl Server 2000), but it didn't solve the problem....

Any other ideas?  Are there any online sql conversion or validation tools that migt be useful?

Thanks
0
champ1979
7/25/2006 4:33:45 PM

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

0
Yubo
7/25/2006 5:16:06 PM

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.

 
 

0
sunny_popali
11/29/2007 12:28:06 PM

Hi,

This is what I want. Thank for the suggestion. I will try it. 

0
inesense
12/3/2007 2:21:38 AM

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

 


Coding is a nine-to-five job: Nine PM to Five AM.
0
pettrer
12/18/2007 1:38:16 PM

 I find Microsoft SQL Server Database Publishing Wizard 1.1 is a much easier/better solution

 SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.

Optionally, SQL Server Database Publishing Wizard can integrate directly into Visual Studio 2005 and/or Visual Web Developer 2005 allowing easy publishing of databases from within the development environment.

http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en 

0
gofreddo
3/14/2008 4:43:29 AM

 hello....

thanx for your solution... 

i got everything..

but not the views

can you please explain how to do it.. ?

Thanx

 


Bhaarat

Remember to click "Mark as Answer" on the post that helps U

My Blog
0
Bhaarat
1/31/2009 5:09:02 AM
Reply:

Similar Artilces:

SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well.  (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed.  I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...

SQL Server 2000 and SQL Server 2005 in the same server
Can enyone please send me a link to a white paper explaining how to run both SQL Server 2000 and 2005 in the same machine?Thanks. I don't know about the white paper but I had both running on my machine for a while by making the SQL 2000 a named instance. So for SQL 2005 connection strings my server name was (local) while for SQL 200 using the named instance it was (local)\joesql where joesql was the instance nameHope it helps,Joe http://www.joeaudette.comhttp://www.mojoportal.com...

This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers
When I connect SQL Server 2005 to a remote database i get the error: "This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers". Can I confirm it is because: the remote db is of SQL Server 2008? There is a soln mentioned in http://forums.asp.net/t/1324047.aspx Did anybody try this (installing 2008 express: http://www.microsoft.com/express/sql/download/)? I am just checking again, whether it will disturb my existing SQL Server installation, in any means...? Though it is mentioned that it will g...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi,Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet? thanks  Place your .MDF file in the App_Data directory, change the connection string accordingly and then publish your web site. ----------------------------------------------------------Please click Mark As Answer if this helped in solving your problem.  Please click "Mark As Answer" if this hepled in solving your problem. ...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf files ...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

sql server 2005 to sql server 2000
Due to certain unavoidable reasons we have to move an application from sql server 2005 to sql server 2000 (yes from 2005 to 2000).  would changing the connection string be sufficient or are there other issues involved. How hard is it going to be to get it to work? Any tips or suggestions are greatly appreciated. Yes you will need to change the connection string!However you must look very carefully at the database structure. Do two script exports of every object in the database - one for SQL2000 compatibility and one without. Compare these two scripts, if they are significantly differe...

Move from SQL Server 2000 to SQL Server 2005 with VS 2005
 We have a new database server for SQL Server 2005.  To use our old VS 2005 websites, which were connected toanother database server with SQL Server 2000, what should I do?  Just change the server name of the connection stringin the Source code and/or Web.config?  My preliminary testing seemed to be not effective.TIA,Jeffrey wonjartran:  We have a new database server for SQL Server 2005.  To use our old VS 2005 websites, which were connected toanother database server with SQL Server 2000, what should I do?  Just change the server name of the connectio...

SQL Server 2000
Hi We have SQL Server 2000 installed on Windows Server 2003. Now wanted to upgrade to SQL Server 2005. Have tried simply inserting SQL Server 2005 dvd and installing on Windows Server 2003. Installation was not successful.  Let me know standard methodology to upgrade SQL Server 2000 to 2005 . Thanks,-- "Mark As Answer" if my reply helped you -- Friend, this tutorial may help you http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1188595,00.html http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1194265,00.html http://msdn.microsoft.com/en-us/...

Sql Server 2000 & Sql Server 2005
Dear AllIs there is a big defference between Sql Server 2000 and Sql Server 2005 and if  i studying thesql server 2000 about  40% or 50% from it..can i upgrade and styding 2005 or i must completing studying sql server 2000 more enough.Please i want a good and practical advise.(i can't get the chance to develope a real life database with sql server2000 until this moment.)Many Thanks 4 All. Hey,A lot of the concepts that are in SQL 2000 will still apply, such as data types, table design, indexes, pk/fk relationships, and such...  DTS has changed completely; in a...

check sql server 2000 or sql server 2005
Hi.. I have a web application(ASP.NET 2.0 + C#). I have sql server 2000 as well as sql server 2005. Several queries in my application work well with the sql server 2005, and the same query fails in the sql server 2000. How to check if the database is connected to sql server 2000 or sql server 2005 and then call the queries accordingly through the c# code? Thanks That may be happening if you are using built-in stored procedures or the INFORMATION_SCHEMA from 2005.  There's stuff in 2005 that doesn't exist in 2000.  You are the one establishing the connection, right...

Connecting to SQL Server 2000 with SQL Server 2005
I currently have Enterprise 2000 installed but would like to uninstall and install SQL Server 2005. Are there any problems if I try to reconnect to other SQL 2000 servers or other serious issues I need to consider before doing this change ?   Thanks,...

sql server 2000 , sql server 2005 express
Hi, currently I m using vs studio 2005 to develop a in house web application... The problem is tat my company has only sql server 2000 license... n when creating the application cos vs studio comes together w sql server 2005 express, so i used it to develop the application. However, i tink there are compatibiliy issues with both databases. I used sql 2005 express mainly because in vs studio 2005 there are the LOGIN tools in which i cant create and they come mapped to databases created using sql 2005 express... so is there any compatibility issues when i deploy this application? and if so, is...

Web resources about - Convert Sql Server 2005 to Sql Server 2000 - asp.net.sql-datasource

File:Convert to SVG and move to Commons.svg - Wikipedia, the free encyclopedia
As a courtesy (but not a requirement), please e-mail me or leave a note on my talk page if you use this image outside of Wikipedia. Thanks! As ...

Facebook Co-Founder Dustin Moskovitz Sells 450K Shares, Converts 7M More
The great Facebook stock sale frenzy continues on with a report that Co-Founder Dustin Moskovitz unloaded 450,000 shares in the social network ...

Facebook Announces “App2User” Liquidity Program for Merchants to Convert Rewards Points into Credits
... for Credits, just announced a new Credits program called “App2User,” designed to enable merchants and loyalty program operators to convert their ...

How to Convert Leads Into Buying Customers - The 9 Step Sales Process EVERYONE Should Use
... School of Business in Toronto on the topics of Sales and Entrepreneurship. I shared with them the sales process that I find How to Convert Leads ...

Bitcoin : Should I convert my savings to Bitcoin?
Answer (1 of 7): You should think of this question as "Should I invest all my savings in Bitcoin?", and not "Should I have my savings denominated ...

Convertizo 2 - Convert Units and Currency in Style on the App Store on iTunes
Get Convertizo 2 - Convert Units and Currency in Style on the App Store. See screenshots and ratings, and read customer reviews.

Catholics should not convert Jews, says Vatican, in push against anti-Semitism
... 50 years since the Vatican's repudiation of the concept of Jewish guilt for Jesus' death.  Vatican City: Catholics should not try to convert ...

Opinion: These 5 things made me a happy iPad Pro convert
... like I needed a bigger device. But after three weeks with my iPad Pro, several things have changed my mind, and I’m officially a happy convert ...

$100 device converts all your film to digital photos
... still on film negatives. Today, we've got the perfect Amazon Cyber Week deal for you: A Wolverine F2D Mighty 20MP 7-in-1 Film to Digital Converter ...

"ISIS 'ranks' the women, considering foreign women and converts to be especially 'valuable.' "
"According to French journalist Anna Erelle’s recent exposé , ISIS foreign fighters prefer foreign women and converts because the jihadists find ...

Resources last updated: 12/19/2015 2:17:21 PM