Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Posts
    11

    Unanswered: SQL upgrade and Domain change

    I have a client who we are upgrading from ms sql 7 to 2000. At the same time we are doing this, we are moving off an old domain, and old servers.

    When I try to use DTS to move the whole database, it fails becaue the users don't exist on the new domain ( ie.. olddomain\jay is not a user. That user is now newdomain\jay).

    What can I do to migrate the databases and not the permissions?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Can't you do a sp_detach_db. Move the files to the new server. Do a sp_attach_db. Create your new logins and delete the old ones.

    Alternatively, it is tedious and the path is frought with danger but you could right click on your databases, go to all tasks, go to generate sql scripts and you can script out the whole database automatically. Dump all of the data out of the SQL 7 database using DTS into text files.

    use the aforementioned SQL scripts to recreate your databases (make sure you did'nt script the old logins) and DTS your data back in.

    beware. you may have to disable triggers, constraints, primary and foreign keys and the like in the rebuilt database before reimporting the data. Then of course you would want to recreate these objects.

    If I knew more about domains and networks and the like, there might be another solution. I do know joining the SQL 7 machine to the new machine will seriously screw up your SQL Server registration because it is the equivalent of renaming your machine and the database may not be recoverable. IN SQL 2K I think you can fix this but I forget how. If you find out you could just upgrade the SQL7 box, join to the new domain, do the fix and make the transfer and you should be right as rain.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Perhaps use sa instead?



    I know. "How retro"

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Was it failing because of the login he was using for the DTS was not working or because the logins he was transferring did not exist on the new server (or would the latter not matter)?

    What was the error message?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2005
    Posts
    11
    Quote Originally Posted by Thrasymachus
    Was it failing because of the login he was using for the DTS was not working or because the logins he was transferring did not exist on the new server (or would the latter not matter)?

    What was the error message?

    It was not failing because of my user id. It was failing because of the logins I was transfering don't exist on the new server.

  6. #6
    Join Date
    Sep 2004
    Location
    Birmingham, UK
    Posts
    18
    This may be a stupid question and if it is I would appreciate you telling me why, but can you not restore the database backup from sql 7 to 2000 and then fix any orphoned users etc?

  7. #7
    Join Date
    Feb 2005
    Posts
    11
    I think I am going to try the sp_detach_db

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    try joining the old db sql 7 to the new domain
    update your logins for the new domain (you will have to do that anyway. might as well do that in place.)
    then perform dts database transfer task
    or detach_db and attach at new server.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Ruprect
    try joining the old db sql 7 to the new domain
    When I did this on a local install on my laptop things went really bad.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    ????

    explain

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Ruprect
    ????

    explain
    I had a local install of sql 7 on my laptop a few years ago. I changed companies and joined my laptop to the new domain and I could not recover my SQL Server Registration and I had to reinstall. My skills and knowledge have grown since then but even the senoir dba said I was screwed. Maybe there was a solution but between the 2 of us we could not figure it out.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    the same thing happens when you rename the server SQL 7 is on.

    the solution is simple, insert your sql server cd and rerun the install. the install would have detected an exisiting sql server installation told you that it could perform an UPGRADE and then update your registry to accept the new SQL Server Parameters. it takes about 30 seconds.

    you dont have to do that in SQL 2000 but you do have an advanced option to refresh your registry settings in the installation wizard if they become corrupted.

    FYI for next time.

Posting Permissions

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