Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2007
    Posts
    13

    Unanswered: Move entire SQL Server instance

    I have tried searching all over and have not been able to find what I need, but this is what I have pieced together.

    Background:
    I need to move an entire instance of SQL Server 2000 SP3 running on Windows Server 2000 to a new physical server running SQL Server 2000 SP4 running on Windows Server 2003. I guess my biggest question is how do I move system databases (master,msdb,tempdb,model), I don't particularly like the idea of using sp_detach/sp_attach on system databases?

    This is what I had in mind.
    -Install SP4 on the source server
    -Backup source to tape
    -Restore from tape to destination server (restore system dbs as re_master,re_msdb)
    -Shut SQL services down on destination server.
    -Replace the system dbs .mdf and .ldf with the re_master & re_msdb .mdf and .ldf
    -Restart SQL services
    -Restore User databases from tape.

    Ultimately, I'm asking can you change the undlying .mdf & .ldf files for system databases?

    Also, are there any issues going from Windows Server 2000 to Windows Server 2003?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    gee, why would you want to move temp? most people would not need to move model either.

    I would...

    1. backup your source user databases.
    2. restore them to the new server.
    3. migrate what you need from master and msdb and you can do some of this with DTS I believe and you can have EM script some other things. It really depends on your needs.
    “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
    You can also backup and restore the msdb database, if you have large numbers of jobs and/or DTS packages (shudder) to move. You will have to go in and update the sysjobs table, so the SQL Agent on the new machine does not think it is hosting jobs from a master job server, though. There is no good way to go through jobs and DTS packages for all the hard-coded server names, though.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    I've migrated lots of instances (from non-clustered to clustered, from old h/w to new) and have never attempted to restore a backup of master, model or msdb.

    - Most of master gets updated when databases are restored/attached.
    - Jobs can be scripted (keep new db names the same as old)
    - DTS packages can be backed up using a script (and moved using a variation of the same script)

    I'm always leery of restoring master, model or msdb because you might get some ugliness in there as well.

    Just my $.02.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    something else that has worked in the past ...
    1. Build out your new server.
    2. Stop sql server on both old and new servers.
    3. Rename the master mdf and ldf on the new server
    4. Copy the master mdf and ldf from the old server to the new server in the location specified for the new server
    5. Fire up the new server.
    6. Modify sysservers for the new server name (unless you will be using the same server name).
    7. Use microsoft methods for moving temp and restoring msdb
    8. Detach and reattach the user database files, or backup and restore the user database file, whichever you prefer.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    I'd take hmscott's advice:
    - Install the new instance
    - restore all user databases
    - Script everything else you need from the old instance

    Because:
    - Master: only contains the logins needed, just script them. If you use the same sid's in the script you don't even have to use sp_change_users_login (when you only have Windows Authentication you don't even have to do that)
    - Model: most people don't do anything special with model and leave it standard. If you have customized stuff in there do a compare and script it.
    - msdb: script (jobs, DTS, etc)! If you really want to, this is the only one I'd restore (prepare for some problems though).
    - tempdb: don't touch it, it's useless to try. Each restart will empty tempdb so there's nothing in there to re-use. If there is, you're doing something wrong.

  7. #7
    Join Date
    Jan 2007
    Posts
    13
    Thanks for all the insight, in case you couldn't tell this is my first migration and I'm just trying to make sure I don't miss anything. I'll let you know what I did and how it turned out.

  8. #8
    Join Date
    Jan 2007
    Posts
    13
    Am I understanding this correctly, that I don't have to install SP4 on the source server if I take Lexiflex & hmscott's advice?

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    that's correct
    “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
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by apt78
    Am I understanding this correctly, that I don't have to install SP4 on the source server if I take Lexiflex & hmscott's advice?
    Why wouldn't you want to install SP4?

    Btw: if the old server has logshipping, replication or linked servers don't forget to script or rebuild them too. They're also in master...

  11. #11
    Join Date
    Jan 2007
    Posts
    13
    I probably will install SP4 on the source. I was just wondering because the source server is going to get wiped out and have a clean install of Windows Server 2003 and new installation of SQL anyways.

    No logshipping, replication or linked servers to worry about.

  12. #12
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Sorry, I read your post too quickly. You said source-server not target

    You do not need to install SP4 on the source.

Posting Permissions

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