Post reply

moving DB

  • May 3, 2004 at 2:38 pm

    #82574

    Hi friends

    I want move our sql server 2000 database from development server to production server.

    i am planning use copy database wizard in EM.

    am i doing right thing?is there any better way of doing it.

    Thanks

  • May 3, 2004 at 3:14 pm

    #505044

    If you can take your server offline for a while, try detach the database  and then attach the database to your production server (using SQL serer Enterprise).

    Note:

    Don't mess with transaction log file of the database once you detach the database, we had a nightmare once.

  • May 4, 2004 at 6:25 am

    #505115

    Don't forget that you will also have to run sp_change_users_login to reset the SID for each login for the database.  The login names will look the same but as we all know it is the SID that SQL/Windows uses to autheticate a user by.  Here is an example for fixing a login:

         sp_change_users_login 'Auto_Fix', 'LoginID'

    To see a list of Login IDs that don't match the recorded SIDs, run:

         sp_change_users_login 'Report'

  • May 4, 2004 at 2:44 pm

    #505203

    Thank u very much for ur posts.

    I finally did backup of develop server and i created a database in production server with same name as  develop server.

    and did restore on production server.finally i used DTS to tranfer logins.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply