January 20, 2008 at 9:47 pm
Did SQLCompare mess this up?
1) I detached a db from a SQL2000 server
2) I zipped the mdf and ldf
3) I moved it to another server
4) I unzipped and attached it to SQL Server 2005
5) I worked with the DB a bit including changing the schema using SQLCompare
6) I detached the db from 2005
7) I zipped it and moved it back to the original server
8) I unzipped it and tried to attach it to 2005
9) I got Error 602: Could not find row in sysindexes
I've done this dozens of times with no problem, the only new thing is changing the schema using SQLCompare. I didn't use any SQL2005 features.
I checked the database while attached to SQl 2005... In Options it continues to show: Compatibility Level: SQL Server 2000(80).
Yet everywhere I look it appears that I am trying to attach a SQL Server 2005 database to SQL Server 2000, yielding this error.
So did changing the schema forever mark the db as a 2005 db?? Can't I pull this stunt off one more time?
TIA
January 20, 2008 at 11:27 pm
what are the schema changes u made.
"Keep Trying"
January 20, 2008 at 11:33 pm
It's not possible to detach a db from 2005 and attach it to 2000, even if you make zero changes to it and even if the compatibility level is 80. The compatibility level is merely there to make 2005 act like 2000 when there were some quirks in behaviour that microsoft didn't want to retain going forward.
Any database that you attach to 2005 is upgraded upon the attachment - you cannot get around this.
The best way to move the database back to 2000 is to use DTS or SSIS to script the tables, etc and then pump the data. I haven't used SQLCompare but I suspect it could help you with this...?
January 21, 2008 at 12:07 am
I said I was sure that I had moved the db's back before, but now you have me doubting it. What you say must simply be true, I now have dbs that are in SQL 2005 and thus are useless to me, since I can't put them back where I got them.
I guess I shoulda kept SQL 2000 around on my dev machine.
The schema changes were simply changing varchar(100) to 200, varchar to ext, a couple of tables had more columns, some new stored procs, all the stuff SQL compare found for me by comparing the two databases.
In hindsight, it's possible SQLCOmpare would have worked directly on the SQL 200 db without me moving it.
Anyone know?
January 21, 2008 at 12:14 am
Answred my own question... SQLCompare would have upgraded my live db (SQL2000) to match my dev db (SQL2005) within reason.
Rats, did it the hard way.
April 24, 2008 at 9:59 am
Excellent question - a client asked me that today and I couldnt answer so I tried it. Gives an error to run DBCC Checktable to check sysindexes. Definitely cannot go backwards, even in 8.0 compat. So for example (upgrade scenario), if you had a cluster and you added additional nodes and removed them as a resource, if the 2005 instances didn't work properly you couldn't simply detach/attach back to 2000 nodes.
Thanks much,
Lee Everest
SQLRx.com
Blog>>> http://www.texastoo.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply