Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: Moving system database

    What is the correct order:

    Should it be:

    Master
    Model
    MSDB
    Tempdb

    I understand that the model should be before the MSDB database but as for master and tempdb I don't know the order.

    Thanks

    Lystra

  2. #2
    Join Date
    Dec 2003
    Location
    Virginia
    Posts
    5

    use 'Alter Database' on tempdb

    Personally, I would use Alter Database on tempdb first using the 'Alter database' statement. Then stop/restart SQL before going on just to follow the practice of 1 change at a time.

    Then follow the steps outlined in http://support.microsoft.com/default...b;en-us;224071 for moving the others.

    Have fun!
    OSCI

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What the hell are you guys talking about?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    If you are migrating master to another server, you want the tempdb files to be in place before you restore the master database. If you will be creating new databases, then model should go next because it is your template. msdb will carry across your jobs and DTS packages, but you will have to modify the originating_server column in the sysjobs table if the new server has a different name that the source server.

    Also, you could stop sql server, and copy the source master mdf and ldf to the corresponding location on the new server and then start sql server. It does work, because I had to do that this weekend for a large server migration. Just make sure you do the sp_dropsever and sp_addserver to rename the sql server from the old to the new!

  5. #5
    Join Date
    Jul 2004
    Posts
    191
    Okay, but does it also apply for moving files from drive c to drive d?

    Thanks

    Lystra

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Nope, you will have to do a restore with move to have it see the files in another location.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've always had good luck with Microsoft's directions, even though I get the willies just thinking about needing to do it with production systems.

    -PatP

  8. #8
    Join Date
    Jul 2004
    Posts
    191
    That was what I was going to use, but now I am totally confuse with the RESTORE WITH MOVE.


    As for the MS direction when moving the master database the example shows changing from the d drive to the e drive. Not from server to server.

    I think know the order to move these databases.

    1 tempdb
    2 master
    3 model
    4 msdb

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Pat Phelan
    I've always had good luck with Microsoft's directions, even though I get the willies just thinking about needing to do it with production systems.

    -PatP
    Sorry ... my answer was incomplete. For the master database, I would use Microsoft's solution ... it works, I can attest to that.

    For the master and model databases, you will have to use the restore with move option to move them from the c to the d drive!

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you doing this?

    Just to migrate users and packages?

    Why not just do a ground up install?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2004
    Posts
    191
    The issue is that when SQL Server was installed all of the system tables were installed on the c drive. The c drive is limited with the amount of space, it's on 16 GB, as for the Server Manager he cannot add anymore space to the drive with out having to re-installation of MSWindows and all of the applications that is necessary on this server. (I don't know how true that statement is). So it was decided by the powers that be to move the system tables off of the C drive.

    So I am trying to get the best possible way to accomplish this goal.

    Thanks

    Lystra

Posting Permissions

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