Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL Migration

Options
  • 30-03-2006 11:55am
    #1
    DubOnHoliday
    Registered Users Posts: 1,292 ✭✭✭


    just looking for other opinions on this. I'm doing a simple move of Sql server 2000 dbs from one server to a new one, no change in sql version. Is this the best way to do it?.... detach from old server, copy the databade and log files to new server and re-attach? what then is the best way to copy the users/jobs etc.... as when you do it this way, you lose the user rights and have to set them all up again with their permissions etc... does anyone have a standard migration procedure? Thanks for any help.


Comments

  • Options
    #2
    bonkey
    Registered Users Posts: 15,443 ✭✭✭✭bonkey


    Bring master and msdb across as well. This contains all login/job data, as far as I remember.

    The following is probably useful :

    http://support.microsoft.com/default.aspx?scid=kb;en-us;224071


  • Options
    #3
    DubOnHoliday
    Registered Users Posts: 1,292 ✭✭✭DubOnHoliday


    that'll do nicely, thank you.


  • Options
    #4
    TheMonster
    Closed Accounts Posts: 1,057 ✭✭✭TheMonster


    I find this useful also

    http://www.dbmaint.com/SyncSqlLogins.asp
    When you copy a database between two SQL Servers (using backup/restore or detach/attach) the users for SQL Server logins becomes orphaned to their respective login. This is because the connection between a user and a login is the SID value generated when you add the login to the SQL Server.
    This utility allow you to re-establih that connection. Just select the database and all orhpaned users are presented in a drop-down box allowing easy mapping to the corresponding login. You can also map all orpaned users in a database for which there exists a login with the name name, all in one go.
    Sync SQL Logins uses the 'sp_change_users_logins' system stored procedure which comes with SQL Server 7.0 and SQL Server 2000.


  • Options
    #5
    bonkey
    Registered Users Posts: 15,443 ✭✭✭✭bonkey


    Heh. That brings back memories....

    I used to hack the tables in master by hand. Terrible, terrible practice, I know, and not one I'd ever suggest to anyone......but damn did it make solving some problems easy :)

    jc


  • Options
    #6
    DubOnHoliday
    Registered Users Posts: 1,292 ✭✭✭DubOnHoliday


    here's my feedback after doing the migration.

    I followed the Microsoft doc ....http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

    which worked fine until the last stage, moving the master db.... I tried to follow this procedure twice. When I tried to restart the sql server service, it would not start not matter what I did. Therefore I had to uninstall and reinstall the sql server again from scratch (such a waste of time)! Same thing happened twice as I said.
    So in the end I just gave up copying the master. I copied all the user dbs and the msdb db then I did a DTS to transfer the logins and I scripted the jobs from my old server and copied them across. After that I had to use sp_change_users_login to correct the ophaned users. All is fine, but there has to be a better way!


  • Advertisement
Advertisement