migrate db from MS SQL 6.5 to version 2005

[babeo]

Hello,
I have some databases need to migrate from MS SQL version 6.5 to version 2005, what steps should I do?
and what I should pay attention to the tasks?

Thanks

 

[SQLSister]

I've not yet gone to 2005 so I'm not sure of the steps, but I bet a search of the Microsoft knowledgebase will give them to you. The single most important step is to back up your databases before trying anything like this.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[johnherman]

You might consider using DTS if you are going to a new server. I think DTS can read from a 6.5 server.

If upgrading the same server, get your database backup(s) as SQL Sister recommended and follow th Microsoft instructions.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[jrbarnett]

I've recently done a 6.5 to 2000 upgrade. I set up a test W2k / SQL 6.5 box, restored the data backup from here on the server, then used DTS (from 2000) to pull it through to a new database on a SQL 2000 box.

After a little testing comparing numbers of records and actual random data samples across both servers, I was happy it was over correctly.
It took about 3 hours all in from once the test server was up to convert and verify 500Mb of data.

You would probably be able to do 2005 using a similar method.

John

 

[mrdenny]

You can use SSIS to migrate the data accross. Another option would be to use the 6.5 GUI to script out the structure of the tables. Then recreate the schema on the SQL 2005 server. Then BCP out the data to text files. One file per object, then bcp the data back into the SQL 2005 server.

The later is the method I used when moving from SQL 6.5 to SQL 2000.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

 

[babeo]

Thanks all for your responses.

I used to do as Denny, but not so sure the bcp is working on my old version - don't know why (commit: I transfer all data into a flat spread sheet and use Access to import it back !!, yes, absolutely I script out the structure of the database and recreate it on the new server), however I really prefer the medthod of John, looks like it is faster and easier.

In any way, 2000 or 2005 are all news to me, so I still need time to play with these animals first, so let me try to play around with it, if I really got stuck, I will run into here and call for help.

Actually, I would like to know how to connect 2 systems, I mean how do I make the server with version 2005 can see the other? or what ever the other way around that works? if any one know? I did try, but unsucessful !!

Thanks again.

 

[mrdenny]

You can create a linked server from the SQL 2005 server to the SQL 6.5 server. You may be able to make a remote server from the SQL 6.5 server to the SQL 2005 server but I'm not sure if it'll work.

You won't be able to use the same GUI to admin both servers. You'll have to use the 6.5 and the 2005 GUI to manage the respective servers.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com