SQL 2005 to SQL 2000
-
I run several Client sites with a mixture of SQL 2000 and SQL 2005, 15x 2005 and 35x 2000 servers in total. I've been developing a database app on a server running 2005 because that's all that I had handy at the time I started and stupidly assumed that the database backups would be backwards compatible, so it would be a simple case to restore onto 2000 so long as the code was compatible.. Duh!
I'm now in a position to start testing this, and tried to restore a backup to a 2000 system and got an error stating that the Structure is version 611 and the server supports up to version 539.
I'm aware that there are long ways round this which I'll take if neccesary, but I'd really like to keep this simple, can anyone give me a tip as to how to run a database backup so that it comes out in SQL2000 compatible mode please?
Pretty Please? 
-
Unfortunate you cant resotre a backup from sql 2005 to sql 2000. its not supported.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
If you need data moved over should be able to use DTS to move it to a 2000 server. If you just need the structure script it out and run on a 2000 server.
-
Damn.. I was hoping there was a much simpler solution.. This is going to create a bit more work than I'd hoped for..
DTS is fine once I have the structure in place, unfortunately, each new version incorporates a slightly different structure as the app develops, and being spread out among different small business clients, they also have nobody on site to do the work.. I can just about manage to explain how to restore a database to a Null DBA, but where do you start with configuring DTS?? If I can't find an easy way to do this, I'm going to have to visit each clients site and configure remote access, which will be popular, because then they'll expect free support on other issues!
I can't believe M$ didn't make an easy way to back up the SS2005 database in SS2000 compatible format. The underlying structure of the code to create the databases is basically the same. I can save Word 2007 Documents in Word 2003 compatable format, although I get warnings of possible loss of functionality. This is So STOOOOPIDD! Anyone aware of any shareware/freeware out there that will do the trick?
-
they make changes to user databases as part of the upgrade so there is no going back. i don't think they ever did it with any of their products
-
How much data are you talking? You could make a change script that would include inserts (and updates and deletes). If you can talk them trough how to do a restore, then certainly a script would be no worse?
Or, you could install SQL 2000 and develop on that
That way you would have to do a one time move. Should be able to install SQL 2000 as an instance on the same machine you have your 2005 environment on.Or, you could upgrade the clients to 2005!
-
All very helpful comments, but SQL_Noob's right, I'd forgotten about them separating the Schema from the Userbase.. Damn.
Restore was to be done via an OSQL in a batch file. "Log in as the admin, go to this FTP address, download the zip file and double click on it." Winzip exe's was supposed to do the rest.
Looks like there's no easy way to do this.. I'll just have to get coding! Hey ho..
Thanks Anders, but I don't want to develop on 2000, 2005 is a heap load better, and I have a licence for it!
The App gets developed and tested on an internal production machine by a lucky selection of users, then bug fixed then rolled out to the rest of our production team. After a second round of bug fixing, it goes on to our clients.. We are slowly upgrading them all to 2005, but $$$ is the issue here! They've bought into a new product, they want it to work in the environment they have already.. I was just looking for a shortcut, looks like there isn't one.
Thanks SQL_Noob.. You made my day
-
The underlying structure of the code to create the databases is basically the same.
The code may be basically the same, but the structure of the database, specifically around the system tables is not. Under the surface a SQL 2005 database and a SQL 2000 database look completely different.
As for deploying to your users, maybe a script and some bcp files that you can have osql automatically run, maybe via a batch file?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ummm, Thanks Gail, but isn't that what's already been suggested? SQL_Noob pointed out the separation of the Schema from the users, which I'd forgotten about, and I think it was me that suggested using OSQL and scripts.. Along with Anders.
I'd asked if there was a way to run a backup on an SQL 2005 box so that it was SQL2000 compatable. It was pointed out by Sugesh that this was not posible, and SQL_Noob explained why. If I'm not mistaken, that answers my question.
Anders suggested that I use a script to create the structure and use DTS to insert the data and GilaMonster added BCP into the equation for good measure.. Which gives me some pointers as to where to go next.
Thanks everyone.. I think all my questions have been answered.
-
Daft(Might not be your right name)
You are in the right forum for asking questions in SQL Server. You will find answers for all ur questions here.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Have you thought about replications?
SQL 2005 would be a publisher and SQL 2000 would be a subscriber.
-
I've thought about a lot of things, buying shares in a strip club
, hanging out on Koh Phi Phi 
, diving with Great White Sharks 
, taking up paragliding 
and a few other things, they all seemed like good ideas at the time, but won't solve this problem.. Neither will replication in this situation because the networks are disparate, the firewalls are not within our control, and the Database schema changes with most of the updates.. But thanks for the suggestion.
If anyone knows where I can get a Thai massage in Switzerland, I'd be most grateful.. That might help ease the stress!
In the meanwhile, I'm opting for the scripting and BCP approach! -
Here's a way out idea.
How about replication to a 2000 server that you control? Then you can take a backup of that and send it off. Even if you just do a snapshot when you're ready to distribute.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Interesting idea but I'm not sure it'd work any better than DTS to be honest, and the scripting of the database will still need to be done, and it's the database structure rather than the data that's important..
-
Wouldn't need any scripting.
Set up snapshot replication from the 2005 machine to a 2000 machine. Snapshot copies the entire database across, structure and data. Then back that 2000 database up and distribute.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply