SQL Server Performance

Moving data from SQL 2000 to SQL 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by Akthar, Jul 16, 2006.

  1. Akthar New Member

    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
  2. dineshasanka Moderator

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


    ----------------------------------------

  3. Akthar New Member

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

    AKTHAR
  4. Akthar New Member

    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
  5. merrillaldrich New Member

    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/

  6. Akthar New Member

    ok

    AKTHAR
  7. Akthar New Member

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

    AKTHAR
  8. satya Moderator

    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.
  9. Akthar New Member

    Becasuse it have to be done on a new server

    AKTHAR
  10. seanlo@jdg.co.za New Member

    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
  11. Akthar New Member

    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
  12. pcsql New Member

    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
  13. satya Moderator

    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.
  14. Akthar New Member

    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
  15. dineshasanka Moderator

  16. Akthar New Member

    Thanks

    AKTHAR

Share This Page