Convert Database 2000 to Database 7.0

[kim1]

Hi,

Is it possible to convert SQL Server 2000 database back to 7.0?

Thanks for your help.

Kim Kim

 

[BeckahC]

Was it originally a 7.0 database? and have you changed anything in it while it's been in 2000? Also, if it was 7.0, do you have a recent copy from when it was still in 7.0? BeckahC

 

[kim1]

I took a version of 7.0 and restore in 2000, then I did a lot of modifications in 2000. Now I have to restore the copy modified of 2000 back to SQL Server 7.0.

Thanks BeckahC Kim

 

[BeckahC]

I'm not sure if it will work or not... I think it might, but I'd ack-up the database first, just in case... I think as long as you didn't use any code that is new in 2000 you should be ok... but I do not know for sure if it will work... you may want to try it on a test server first, so that way you still have the database backed-up and safe in case it doesn't work right.

Hope this helps! BeckahC

 

[tlbroadbent]

SQL 2000 databases are not backward compatible.

You'll need to transafer the database to SQL 7 using DTS. ALternately, you can script the database and export all data using BCP or DTS. The create the DB in SQL 7 and import the data. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[kim1]

Hi Terry,

Thanks for your reply, the modifications that I have made in version 2000 were 15 diagrams.

I don't know if we can import the diagram with DTS?

Kim
Kim

 

[BeckahC]

Sorry, I haven't really worked with 2000 much, I'm really just a 7.0 person. I do know that diagrams do not transfer using 7.0's default DTS transfer options (if going through enterprise manager) and you will need to alter some of the options to get everything to go over (like the logins). BeckahC

 

[tlbroadbent]

Diagram info is stored in the dtproperties table in each database. If you script dtproperties it should the diagrams should transfer. If not you may be able to use USE DTS to transfer the contents of dtproperties from SQL 2000 to SQL 7. However, the table format is slightly diferent so I'm not sure if this is possible. I haven't tried it myself.

You might also run a script like the following.

Insert
-- SQL 7 Server
<server7>.<targetdb>..dtproperties
Select
objectid, property, value,
-- you may need to remove uvalue from the select list
lvalue, version, uvalue
From dtproperties
-- Sql 2000 Server
<sourcedb>..dtproperties Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[kim1]

Thank you very much Terry, I'll try that out.

Kim Kim