Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: best way to copy database

    I need to move a database from sql 2000 to sql 2005. My research suggests that I can backup a sql 2000 db from enterprise manager and restore it with sql 2005 management studio. Are there step by step directions for this or can someone tell me how to do this?
    what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can't find anything in the event log or anywhere else to tell me why. Please help. Thanks

  2. #2
    Join Date
    Feb 2007
    Posts
    71
    IMO, I would probably suggest backup and restore tactic for user databases. As for logins, either you dump the data into a file by doing a BCP out (bulk copy program) on master..syslogins table of the old server and BCP IN to the same table of the new server, however, since I've no experience on SQL2005, please check the structure of master..syslogins table if they're similar on both versions. As for the sysusers of user databases, you can make sp_adduser statements by doing a SELECT 'sp_adduser ' + NAME + ', ' + NAME + ' FROM MASTER..SYSLOGINS

    As for syntax reference, try BOL.

    HTH

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The BCP of syslogins trick quit working in SQL server 7.0. SQL 2005 has an all new system table structure, so I greatly doubt it would work here. For the logins, look up the CREATE LOGIN command. You will want to pay especial attention to the SID parameter. If you keep the SID for each user the same for SQL 2005 as it was on SQL 2000, they will match up with no problems. If you do not supply a SID for the CREATE USER command, a new random SID will be generated, and you will have to drop and recreate the user in the database (or recreate the login with the right SID).

    Any jobs/maintenance plans that will have to come over?

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    I got it over with backup and restore and all the logins are there. No job or maintenance plans need to be restored. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •