Post reply

How to move distmdl.mdf and mssqlsystemresource.mdf

  • September 23, 2008 at 7:13 am

    #124747

    When I installed SQL Server 2005 I moved the master, mdf, model, and temp databases to a different array.

    Since then I have created all my database data files on the same array and all the log files I left in the default install location for SQL server. (I only have a RAID 5 and then mirrored for the OS partition. I wasn't able to get a third array to use for log files sadly....)

    Anyway, I did NOT move distmdl.mdf because I didn't see any documentation about it in books online when I was following the instructions for moving system databases. The same for mssqlsystemresource.mdf.

    So now I have a hotfix that failed:

    http://www.sqlservercentral.com/Forums/Topic573558-146-1.aspx

    And I saw this thread about distmdl:

    http://www.sqlservercentral.com/Forums/Topic220317-146-1.aspx (well actually a poster to my hotfix thread mentioned it and I did a search)

    So I read the thread but I'm still a bit unclear about the best way to do this.

    I assume the following to be true:

    1. Since I'm not using replication the distmdl is NOT being used

    2. I could TECHNICALLY delete the file and not suffer EXCEPT some hotfixes require its presence to install

    3. It needs to be in the same folder as the master database

    4. It doesn't respond to the standard alter or move commands from inside SQL Server.

    That's where my understanding ends.

    I've seen posts saying I can just stop SQL Server, move distmdl.mdf (as in cut and paste in Explorer) and then start SQL Server and all is well.

    However, I also saw posts saying "Eh, just delete it...".

    What is the prefered way to move this database? Do I need to use the alter database command to tell SQL Server where I'm moving it to, or can I just stop MSSQLServer and move it myself then restart?

    What about mssqlsystemresource.mdf - what should I do with this?

    Move it to the same location as the master database?

    Thank you.

    EDIT:

    The article about moving system databases: http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx one of the community notes at the bottom mentioned the need to keep the mssqlsystemresource.mdf WITH the .ldf file and to NOT seperate the datafile from the log file.

    Can anyone validate this claim?

    The article shows moving both to the same location, but I am not clear if that is simply because they are using the same sample location for relocation of ALL files...

  • September 23, 2008 at 8:22 am

    #875694

    That link on that bottom takes you to the right place.

    You're going to have to run ALTER DATABASE mssqlsystemresource

    and the other commands there.

    I can't speak about the distmdl or the split to another drive.

    MCITP, Database Administrator
    A hodgepodge of Information Technology and Life
    LinkedIn Profile
    My Twitter

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply