Move distmdl.ldf and distmdl.mdf

Question

Move distmdl.ldf and distmdl.mdf

barogers

SSC Rookie

Points: 32
More actions
April 23, 2008 at 12:06 pm

#121485

Hello,
We have SQL 2005 without any SP's installed. I am needing to move the distmdl.ldf &mdf files. I have found nothing on how to do it. There was one post on this forum a few years back about it, but there wasn't an answer to my question because they weren't using log shipping. I've moved most of the user databases and planning on moving the system databases soon. When I move the system databases can I simply move these files also and log shipping will pick right up when I restart the DB? If necessary I can delete log shipping settings first and move DB's then setup log shipping again, however I'd like to avoid this if at all possible because the database is about 20GB and the other server is on our WAN connect by a single T1 circuit.
I appreciate any advice on this.
Thank you.
Brad

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

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

these files are used as templates for the distribution database when you use replication, they will be present at every server, you don't need to move them.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

barogers SSC Rookie Points: 32 More actions · Answer 2

Thank you for the reply.

So are you saying I can move them without any issues since I am only using log shipping? I guess I thought they were in use because their modify date that is a week old. I do need to move them, just concerned about how.

Once I have all the system DB's moved can I just move these files also?

Sorry for my lack of knowledge, but I have inherited this situation and trying to learn what I can about SQL.

Thanks.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

If you're not using replication you can ignore them. No need to copy them.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

barogers SSC Rookie Points: 32 More actions · Answer 4

SSCrazy,

Thanks for the help. I have all the database files moved now.

I had a problem with moving the master after starting in single user mode, when I would try to connect to tell it where the mssqlssytemresource files are, it wouldn't let me in. I was finally able to use sqlcmd instead of the studio to get it done, although I don't think that is what the problem was. My only concern is the "alter database mssqlsystemresource set read_only" kept failing saying that the database didn't exist. Everything seems to be working, should I not worry about it?

Thank you.

free_mascot One Orange Chip Points: 27164 More actions · Answer 5

Your problem is not much clear to me.

But I understand is you are having problem in accessing the user databaes. If you are having the problem in accessing user database and sql server is unable to find the path; manually you can attache the database to resolve the issue

Moreover you can refer the following document how to move sql server databases:

http://support.microsoft.com/kb/224071

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546

Hope this will help.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

barogers SSC Rookie Points: 32 More actions · Answer 6

No, I am not having a problem accessing user databases. All seems to be okay. My issue is/was when I issued the following command

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

from http://msdn2.microsoft.com/en-us/library/ms345408.aspx at step 9. There was an error that it could not find the file. Here is the exact text from the .out file.

Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Msg 5069, Level 16, State 1, Server XXX-MACTPRODSQL\PRODUCTION, Line 1

However, the master DB and the mssqlsystemresource DB are in the new location like I need and everything appears to be working correctly.

Do I need to worry about that SET READ_ONLY statement?

Thanks for your help.

Brad

Murat Gökalp SSC Rookie Points: 49 More actions · Answer 7

Hi,

I just did it right now. So I can easily explain. Because of "mssql full text search" and "mssql server agent", in single user mode, connection is occupied by them and you can not connect to the server, although you are the administrator.

1. Stop sql server

2. Start in single user mode in command prompt "net start mssql /f /T3608"

3. Connect sql server as an administrator

4. ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=data, FILENAME= 'G:\Data\BTSINSTANCE\System\mssqlsystemresource.mdf');

GO

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=log, FILENAME= 'f:\Log\BTSINSTANCE\System\mssqlsystemresource.ldf');

GO

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

go

5. Then stop-start sql server normally

Finally my question is :

"If I don move distmdl db files to the same location with master , some sql hotfix (after sp2 for 64 bit Itanium) failed. Any comments ?"