Saturday, February 25, 2012

Restore 2005 to 2000 Database

The machine I do development on has SQL Server 2005 installed.
My client, however, is running SQL Server 2000.
When I tried to restore the database, I got the error:
The backed-up database has on-disk structure version 611.
The server supports version 539 and cannot restore or upgrade the database.

How can I restore a SQL Server 2005 database to a SQL Server 2000 database?
You can not restore or attach a sql2005 db to sql2000.
You would need to export all the data, then import it back.|||That's pretty inconvenient!

Thanks for you help.
|||Ugh, that's awful. Way to make our lives hard, microsoft!
|||

At least the opposit is working!

So take a SQL Server 7, 2000 backup and restore it to a SQL Server 2005 Server. That is not true for master, model, msdb, but user database should work fine.

For doing the restore one has to use the option MOVE

|||

I successfully attached a SQL 2005 database back into SQL 2000. However, that SQL 2005 database was originally from SQL 2000 and its compatibility was set to 80.

|||

It's very interesting. Have you occured this error?:

Error 602: Could not find row in sysindexes for database ID 24, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

We tried to attach our database back to SQL 2000, but this error occured.

|||The datadictonairy has changed completely. I think you should export all application tables in SQL Server 2000 and import them in SQL Server 2000. Be aware of the relationships :-)|||I'll say. That just happened to me also. the database grew from 32gigs to 72gigs since we installed 2005. We only need 2000 to migrate our data to a new version of the software we are using. Test it on 2005 in a test databse for a couple of weeks, then do it all over again for production database. errrrrr..|||

You're asking to restore a much newer version of a database backup into an older versiion of the product. How can you possible expect this to work?

Would you expect Windows XP to run on an XT (Intel 8086) system? Of course not! So why expect the same here?

Your best option is to export the data.

Besides, who in their right mind develops something on a system that does not replicate their customers environment? Sounds like you haven't been in the game for long.

|||

hi there

i'm facing the same problem...

1] I had SQL 2000 database dbTest...

2] attached that dbTest.mdf to sql 2005

3] now when trying to attach that same .mdf to sql 2000, i'm getting following error --

Error 602: Could not find row in sysindexes for database ID 9, object ID 1,
index ID 1. Run DBCC CHECKTABLE on sysindexes.

Can u please tell me how did u get ur error solved? Its a bit urgent...

Thanks in Advance!!

|||

it is possible when you export the data from sql2005 then import to SQL 2000. An alternative method for this is to create 2 named instances within same computer. 1 for SQL 2005 and 1 for SQL 2000 on the same server.

Then export DB from SQL2005 to SQL2000. Afterwards backup your database in SQL2000.

It worked for me.. Yes i agree its a kind of catching the ear by a long way. But its better than it's not working.

I am not sure if Microsoft has announced backward compability of SQL 2005 Db's or supplied a detailed "How to" instruction or not; but they should have "SHOUT IT OUT LOUD" else this brings people anger when Microsoft emancipates itself like "We dont have to supply this compability"

So that; i'm in anger...

|||

My view on this is that exporting from 2005 to 2000 is pretty easy using the export feature in 2005.

While I don't disagree with the view that you should develop on the same technology as your clients, I think most developers see SQL/Server as a collection of tables, views, indexes, relationships, i.e. not particulary using new features from SQL/Server 2005, so its pretty inconvenient not knowing how to do this, and for mixed client environments where some databases are 2005, others 2000, its essential.

Selecting export in 2005 and choosing all the objects, for me worked ok. If I had used a new feature in 2005 then fair enough, but I would not be wanting to export back to 2000 in this circumstance.

|||

It makes me so frustrated!! I like to keep up to date, plus my old dev machine is getting on in years, so I splash out on a new PC with Vista, only to find I can't install SQL 2000 and am forced to use SQL 2005. I set everything up, make some changes to the database for my web app, then find I can't update the database on my web host's server because they're still on SQL 2000! I have no alternative but to run SQL 2000 on the old PC.

People ready!! my arse!

No comments:

Post a Comment