SQL Server Performance Forum – Threads Archive

Moving data from SQL 2000 to SQL 2005

Hi Guys, I got an SQL Server 2000 and i need to move the database to a newly install SQL 2005 Server. I need to move the complete database(Tables+views+Sp..) I need also to move all users. Any best pratice is most welcome. Thanks AKTHAR

What about restoring the database. I have move sql server 2000 data to sql server 2005
—————————————-

Yeah but then what about the logins and access rights on the server? AKTHAR

IN fact i got several application running on the SQL Server 2000 using specific logins, with defined rights on the objects. How can i move the logins with the correcponsding objects access for a smooth transfer! AKTHAR

One method is to "script out" the logins and use the script to load them on the new server. This works OK if the new server is "clean" and does not have a bunch of them already created. See http://support.microsoft.com/kb/246133/ and http://support.microsoft.com/kb/918992/

ok AKTHAR

What about Database Transfer Wizard, anybody use that with SQL 2000 to SQL 2005??? AKTHAR

Why don’t you choose the upgarde path in this case? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Becasuse it have to be done on a new server AKTHAR

You can use the copy database wizard, it will copy all objects from the database , and can also copy all logins/users and jobs etc

Yes. but i will have to do this on Enterprise Manager as it seems that this feature is not longer available in sql 2005.
I am not able to register SQL 2005 Instance on Enterprise Manager.?
Have i miss something somewhere? AKTHAR

Akthar, Are you sure that the Copy Database Wizard is in SQL Server 2005? Right Click when you’re highlighting a non System Database in Management Studio.
Tasks -> Copy Database… I have similar issue earlier because I right click on a System Database.
Peter

IF the both servers on same network then what is the problem in choosing UPGRADE method. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Satya,
What do you exactly mean bt the UPGRADE ? I am setting up a new server where i will install SQL 2005 and i want to move all datas and logins to the new server. Is the Database Copy wizard a good pratice. Thanks for your help
AKTHAR

just for your information about upgrading http://www.sqlservercentral.com/columnists/rpearl/installingandupgradingtosqlserver2005.asp
—————————————-

Thanks AKTHAR

]]>