Are there issues with converting 2000 DB's to v7

[Welshbird]

I've just been seconded to a new part of my company and I now need to take all of my SQL2000 databases and migrate them to the SQL7 server I need to use from now on.

Is there anything I need to be aware of in advance? Are there likely to be any risks in doing this?

Thanks for help in advance.

Fi.

 

[SQLBill]

I've never done this....if it can be done, be aware that version 2000 added more features/functionality. Your scripts and other things may no longer work as expected.

-SQLBill

Posting advice: FAQ481-4875

 

[Welshbird]

Thanks.

I'm guessing that data in tables should all be fine though? There aren't many scripts for me to worry about.

Fi.

 

[SQLBill]

Like I said, I've never done that (never used v7 either). I know there were some changes, such as Table Variables. I don't know if any table structures were changes or if they will affect you.

Best I can say is test it. Make a copy of your database and restore the copy as v7. That way if it doesn't work you still have it as a v2000 db.

-SQLBill

Posting advice: FAQ481-4875

 

[Welshbird]

I was really hoping that I might move the physical files (.mdf & .ldf) and simply re-attach the databases, but I guess I do need to test that in advance.

Generally the databases are simply 'buckets' for data rather than databases that are doing anything especially clever.

Anyone any thoughts?

Fi.

 

[SQLBill]

I'm not sure SQL 7 has the attach database command. But if it does, you could 1)Detach the v2000 database(s), 2) make a copy of the .mdf and .ldf files 3) move the copies to the v7 box 4) attach the copies to the v7 server.

-SQLBill

Posting advice: FAQ481-4875

 

[Corran007]

It has the atach db command, but its only via tsql. no gui for you. check bol for documenation on it

My understanding is that there were either no strucatual database changes from 7-2000 or any ones that exist were automotcailly done when you did an attach or a restore. I would try the attach first. Test Everthing! i would write some scripts to help with doing all this. i would test a select and insert on every table just to make sure, perhaps and update and a delete as well. If an attach fails, then i would try a restore. Repeat testing. If that fails. i would script the databases and crete them from scratch. then just import all the data

good luck with this. and post your results when you finish.

 

[Catadmin]

Actually, there are a ton of structural issues, such as datatypes, stored procedures and extra commands in 2k that aren't in SQL 7. As SQLBill said, test first. I'm not sure that you CAN directly restore 2000 to a 7 box. Looked it up on Google and found the following link.

http://www.lazydba.com/sql/1__3355.html

MOF, I read in Sybex's "SQL Server 2000 Administration Study Guide" that there are some things in SQL 7 that don't translate up to SQL 2k. So, given all this, I'm assuming you can't just do a detach and attach. I think you'll have to pull in a copy of the DB as a new database and I know you can't restore to SQL 7 a SQL 2k backup.


BTW, you can run SQL 7 and SQL 2000 side by side if you install the 2k version in a different directory. Then you can use the SQL Switch to switch between them. Remember, SQL 7 doesn't use named instances, so if you do this, you'll have to have SQL 7 as the default and SQL 2k as a named instance, since only one default runs at a time.

Does this help you at all?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[SonOfEmidec1100]

My view - there is no backwards compatibility from 2000 to 7.

Anything you are going to do you need to do from sql2000.

I would script the database objects, select only scrip 7.0 compatible features in the formatting options.

Then use DTS or maybe BCP to transfer the data.

 

[mrdenny]

There is no backwards compatability from SQL 2000 to SQL 7.

You will need to script your database objects, and recreate them on the SQL 7 server, then DTS your data across.

You can not restore a SQL 2000 database to a SQL 7 server.
You can not attach a SQL 2000 database to a SQL 7 server.

There are structual differences between the SQL 7 and SQL 2000 file formats.

You are in a painfull place here.

Best bet would be to have the new group upgrade the SQL 7 server to 2000 and take advantage of continued support beyond the end of this year, as well as the new features of SQL 2000.

Sorry to be the bearer of bad news.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)