Moving distmdl.mdf and distmdl.ldf files...
-
I know these are for distribution but a new DBA installed SQL wrong and now I have to move some files. I have already moved MASTER, MODEL, MSDB and TEMP but I can't find anything on distmdl.mdf. Anyone know how to move them from the default install directory? Is it worthwhile moving them since we aren't really doing distribution/replication at this point?
Thanks
SJ
-
This is not a critical database, so use the same process you would for any user database that needs to be moved.
The easiest way normally is to detach the database, move the files, then attach it specifying the new file locations.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
If you are not using replicatino simply delete the files from thier locations. If you decide to use replication later, then the databases will be created for you (and you can select the location then). If you are actively using replication then it may get a bit 'stickier' than just detach/move file/attach.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Agree - those are just "model" files. I've never tested to see what happens if the distribution model is missing when you set up a server as a distributor, but it would be easy enough to correct if needed.
-
"Do Not Delete" the distmdl files !
Several months ago, I took the advise of someones post and deleted these "for replication only" files. However, the lastest MS hotfix fails because it can not find these files.
If you look at the resulting log files in "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix" folder you see the following messages:
1. "SQL Server Setup did not have the administrator permissions required to rename a file: d:\MSSQL.1\DATA\distmdl1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account".
2. "Unable to get component path for component distmdl.3D37F4F2_DA2F_4972_97D0_34767EB5DA29 distmdl1.ldf"
Bottom line is that the distmdl files need to exist for updates/hotfixes even if you do not implement replication.
-
The Resource database (distmdl.mdf & distmdl.ldf) depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location as the master data file. See http://msdn.microsoft.com/en-us/library/ms345408.aspx for more information.
-
you can also use the alter database modify file command to move the files, make sure they're in the same location as MASTER as already stated
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Fully agree with Befother do not delete the dist data files. I too had similar error messages when I tried applying earlier Service Packs/Hotfixes.
-
Mark (8/12/2008)
Fully agree with Befother do not delete the dist data files. I too had similar error messages when I tried applying earlier Service Packs/Hotfixes.Same problem here. How to fix this? I can't reinstall replication services, gives me a error telling me the current instance is newer then the version I'm trying to install (probably because of SP2)
Regards,
Jeroen
-
Hi Jeroen,
I think I just copied distmdl.mdf & distmdl.ldf from another server/instance that had not been upgraded back to the server I was trying to upgrade.
Regards,
Mark
-
Thanks Befother and Mark,
I too followed an earlier post to delete the distmdl.mdf and distmdl.ldf files and ran into issues installing SQL Server 2005 SP2 Update (KB948109).
I copied the files from another server and was able to finally install the update.
-
Hi all,
Somehow I got the feeling that the hotfix only checks for the files presence (or better the presence of two files named distmdl.mdf and distmdl.ldf), so I copied and renamed the temp database and log to distmdl.
Tried the hotfix and voila, works! Then deleted the 'distmdl' files. restarted the SQL services, no problems.
Not sure if I can recommend this on a production server (I tried it on a test server), but again, I've not seen any problems yet.
If anything goes wrong, I'll post it.
Regards,
Jeroen
-
Hi,
I was able to use the link to move the resource database and all of the other system databases except the distmdl. I believe the to delete, recreate and then delete again is OK, but not acceptable by my management. I would like to know how to move the distmdl database. I used the alter database for the resource and it worked perfectly fine. The problem I have for the alter database which I used for the resource is the name of the database as referenced by the table it resides in. For instance I used the following command for the resource:
ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = data , FILENAME = 'e:\kaman\data\mssqlsystemresource.mdf' )
ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = log , FILENAME = 'e:\kaman\data\mssqlsystemresource.ldf' )
When I run the sp_helpdb the distmdl database is not on the list. So can anyone fill the name of this database and I would then try the same instructions as I used for the resource since I believe it is the same setup as the resource.
Thanks for any help you can provide.
dbm
-
As a resolution to this issue we uninstalled and reinstalled MSSQL 2005, placing the default datafiles in the directory we desired, since there seemed to be no real solution for the movement of the distmdl files. This was ok for the servers that did not have databases, however the server that had databases I had to reinstall users, jobs, etc. I used scripts for most of the work.
-
As you are not making use of replication at this moment then backup the database, then restore it using the with move option to place the files in different locations.
HTH
MJ
Accepting all invitesViewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply