Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

SQL Migration

  • 30-03-2006 11:55AM
    #1
    DubOnHoliday
    Registered Users, Registered Users 2 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

  • #2
    bonkey
    Registered Users, Registered Users 2 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


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


    that'll do nicely, thank you.


  • #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.


  • #5
    bonkey
    Registered Users, Registered Users 2 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


  • #6
    DubOnHoliday
    Registered Users, Registered Users 2 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