Moving distmdl.mdf and distmdl.ldf files...

  • george sibbald

    SSC Guru

    Points: 104200

    shame you had to go thru all that pain, the simple solution is to just stop SQL, move the distmdl files with explorer and restart SQL.

    ---------------------------------------------------------------------

  • dennylou

    Old Hand

    Points: 304

    But what about the error that comes up when the patch or hot fix is installed and can't find the resource or the distmdl files? This would not be a good thing for us. We only had one server that already had databases and they are in the testing process at this time.

    Thanks for your suggestions. Another company that might work.

  • george sibbald

    SSC Guru

    Points: 104200

    if distmdl in same location as master upgrade will work

    ---------------------------------------------------------------------

  • dennylou

    Old Hand

    Points: 304

    I will test this so we can use it in the future. I believe we have some production servers that don't have our data files where we would like them. Thanks, for your help.

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Hi Folks

    I have the distmdl.mdf and .ldf files on my c drive but they are nor currently attached

    can i just move these two files to their new location after i move the system databases or do i have to attach them first

    Thanks

    Jim

  • Perry Whittle

    SSC Guru

    Points: 233779

    always keep dist and resource database in the same location as master.mdf

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Hi Perry

    got it

    but can i just move these two files since they are not attached, when i move the system databases ?

    Thanks

    Jim

  • Perry Whittle

    SSC Guru

    Points: 233779

    are you using any replication?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Not at this time and no plans to

    Jim

  • Perry Whittle

    SSC Guru

    Points: 233779

    just pick the files up and move them with the master.mdf

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • RML51

    SSCarpal Tunnel

    Points: 4410

    If you're not currently running replication, you probably won't have any problems moving the distribution database files.

    If you were actively running replication, I'd try this:

    -- 1. Stop the Log Reader and Distribution agent jobs on the Distributor.

    -- 2. Take distribution database offline.

    ALTER DATABASE distribution SET OFFLINE

    -- 3. Move distmdl.mdf and distmdl.ldf files to new location (cut/paste).

    -- 4. Modify the database to use the new file location.

    ALTER DATABASE distribution MODIFY FILE (NAME = distmdl,

    FILENAME = 'K:\MSSQL.2\MSSQL\Data\distmdl.mdf')

    ALTER DATABASE distribution MODIFY FILE (NAME = distmdl_log,

    FILENAME = 'K:\MSSQL.2\MSSQL\Data\distmdl_log.ldf')

    -- 5. Bring the distribution database back online.

    ALTER DATABASE distribution SET ONLINE

    -- 6. Restart the Log Reader and Distribution agent jobs for the Distributor.

    Note: I haven't actually done this for the distribution db, but I've used a similiar process to move subscriber dbs to a new drive.

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Thanks Perry

    Jim

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Thanks RML51

    Jim

  • C.J. Morgan

    SSC Enthusiast

    Points: 198

    So I didn't see if someone already corrected you, but DISTMDL databae IS NOT the Resource database and the article you mention has no mention of the distmdl database. You are correct though when stating that the mssqlsystemresource database should be in same physical location as Master according to MS best practices.

    CJ

Viewing 14 posts - 16 through 29 (of 29 total)

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