How to move SQL 2005 data and log files

Hi,

I just installed SQL 2005 on a clean 2003 server. I didn't see a choice to select where the data and logs folders would be, only where the program files would be. The result is that I have everything installed on C: and I want to have data on D: and logs on E:
I didn't install SQL 2005 SP2 yet.
Any suggestions?

Thanks.
drotkopfAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>2. For me, it didn't work detach/attach master.
that is normal/expected, and the method you found is indeed the way to move the master db.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
howto move databases:
support.microsoft.com/kb/224071
0
 
drotkopfAuthor Commented:
Since I'm using SQL 2005, I followed the instructions in http://msdn.microsoft.com/en-us/library/ms345416.aspx in order to add the -c -m -T3608 parameters to the SQL Server startup.
However, when I add them at the beginning of the existing parameter list, SQL Server doesn't start. And if I add them at the end of the existing parameter list, I still have the following error:
Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
What's next?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
drotkopfAuthor Commented:
Well... now I'm in trouble. I ran the following (with good paths) and after restarting SQL server, it doesn't start anymore. And I can't run a new query to put that again as it was...

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE  tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
0
 
drotkopfAuthor Commented:
Is there a clean way to just uninstall everything?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>it doesn't start anymore.
what do you get in the errorlog file?
the path of that file is indicated by the startup parameters...
0
 
drotkopfAuthor Commented:
2008-11-05 21:08:09.19 Server              -m
2008-11-05 21:08:09.19 Server              -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-11-05 21:08:09.19 Server             -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-11-05 21:08:09.19 Server             -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-11-05 21:08:09.19 Server              -c
2008-11-05 21:08:09.19 Server              -T3608
2008-11-05 21:08:09.19 Server      Error: 17113, Severity: 16, State: 1.
2008-11-05 21:08:09.19 Server      Error 3(The system cannot find the path specified.) occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
0
 
drotkopfAuthor Commented:
But
1) My MODIFY FILE statement was for tempdb, not for master
2) master.mdf is in the path specified
3) tempdb is not in the new path that I specified but in the old
As you can see, I have no clue!
0
 
drotkopfAuthor Commented:
I just moved tempdb.mdf and templog.ldf to their "new" locations as I had specified in MODIFY FILE but still have the same error.
0
 
drotkopfAuthor Commented:
help...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> Error 3(The system cannot find the path specified.) occurred while opening file 'master.mdf'
that means that
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
is wrong. did you not say that you moved already those files?
0
 
drotkopfAuthor Commented:
No. Those files are there. I copied (not moved) tempdb.mdf and templog.lgf, but that was after the error, and we still got the error. In other words, copying temp* files did not help.
Again, I didn't touch the Master DB files.
Is there anywhere in the registry where I could find where all this things are pointing to?
Any other options?
Thanks!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

seems to have no space between the -d and the path... can you try to ensure there is a space in the startup setting?
0
 
drotkopfAuthor Commented:
Same thing. Took out other parameters. These are the parameters now:

-d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

And this is the error:
2008-11-06 13:58:25.83 Server      Authentication mode is MIXED.
2008-11-06 13:58:25.83 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-11-06 13:58:25.83 Server      This instance of SQL Server last reported using a process ID of 5992 at 11/6/2008 1:54:36 PM (local) 11/6/2008 6:54:36 PM (UTC). This is an informational message only; no user action is required.
2008-11-06 13:58:25.83 Server      Registry startup parameters:
2008-11-06 13:58:25.83 Server             -d  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-11-06 13:58:25.83 Server             -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-11-06 13:58:25.83 Server             -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-11-06 13:58:25.83 Server      Error: 17113, Severity: 16, State: 1.
2008-11-06 13:58:25.83 Server      Error 3(The system cannot find the path specified.) occurred while opening file ' C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

See the screenshot, the master db files are there.

Thanks.

untitled.JPG
0
 
drotkopfAuthor Commented:
still need help... please...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please understand that we are all volunteers here... and have our private and work life that has some priority...

now, I see that the path/file does exist, still sql server, on startup, complains.
so, are you looking on the same machine for the path than where the sql server runs (and please understand that you would NOT be the first to run into that "error").

0
 
drotkopfAuthor Commented:
yes I am looking at the same machine, and sorry for that, I was just a way to push a new email in case the previous one didn't arrive.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have maybe 2 sql server services running?
how do you start the sql server?
0
 
drotkopfAuthor Commented:
I start it using the SQL Server Configuration Manager.
I don't have 2 sql server service running.
All the problem started after I did this:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'H:\MSSQL\SQLData\tempdb.mdf');
GO
ALTER DATABASE  tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\SQLData\templog.ldf');
GO
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>All the problem started after I did this:

however, that statement cannot result in the error message:
Error 3(The system cannot find the path specified.) occurred while opening file ' C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf'

can you check how many sqlserver.exe processes you have in the task manager, please?
0
 
drotkopfAuthor Commented:
See Services Panel
untitled.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that's not what I asked for, actually...
0
 
drotkopfAuthor Commented:
Sorry. None.
untitled.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
thanks for confirming...
that leaves, for me, only some shots into the blue:
>-d  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
try to make sure it's not a case sensitive matter:
-d  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf

also make sure that both the folder and the file(s) have still file permissions for the sql server service startup account (which is that, btw? localsystem or a windows account?)
0
 
drotkopfAuthor Commented:
angelIII, I had tried that already... and it's all localsystem

I know it makes no sense, but it all started when I did this:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'H:\MSSQL\SQLData\tempdb.mdf');
GO
ALTER DATABASE  tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\SQLData\templog.ldf');
GO

Before doing that, I had seen those parameters and they were exactly like that and I had started and stopped the SQL server many times.

It is possible though, that my paste above is not precise and I had a typo or something. That's why I think it could help to either
(a) Registry editing if it's not in a hundred places
(b) uninstall / reinstall, if possible after the mess I made

Either way, I need
(1) Advise and
(2) Guidance on how to do the solution

I can hear a (c) also if you have one.

I'd love to give this one more points, but 500 is the max!

Thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
(a) -> there is only that single place for the startup parameters
(b) -> yes, that is surely possible. simply uninstall sql server from the software
(c) -> is H: and F: local drives, or mapped drives?
0
 
drotkopfAuthor Commented:
Another opportunity to expose my ignorance... H and G (the typo was in the post here only) "look" local, but I think they are really SAN or NAS or something like that. Should that make a difference?
0
 
drotkopfAuthor Commented:
angelII, I'm stuck. I tried to uninstall, and it doesn't go because it can't access the folder in C, which seems to be a network folder? I modified and gave everyone full rights all the way from Program Files down. What's next?
(It's late -EST- and tomorrow I have to make some visits early in the morning so I'm not sure when I'll be able to log back in)
Thanks.

untitled.JPG
0
 
drotkopfAuthor Commented:
angellIII,

It looks like good news...

I reinstalled SQL server on top of the installed ruins that I had. It still didn't work, but then, I deleted the space between -d and C in
d  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
and then it worked!
I even confirmed that the fmdf and ldf files for the tempdb DB are being used in the new, moved location (G and H).

Back to the original question, though, How do I move the rest of the 3 system databases? Any particualr order?

Thanks!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Back to the original question, though, How do I move the rest of the 3 system databases? Any particualr order?

no.

>H and G (the typo was in the post here only) "look" local, but I think they are really SAN or NAS or something like that. Should that make a difference?

no. SAN/NAS drives are considered "local".
0
 
drotkopfAuthor Commented:
Thanks!!
0
 
drotkopfAuthor Commented:
Hi angelIII,

I had a long jorney with this. I think it's finally done.

In order that you can continue helping people like me please note this 2 things that I found:

1. The MS KBA (support.microsoft.com/kb/224071) is WRONG when saying that you have to add -c -m -T3608 to the Startup Parameters, It has to be like this
        -c;-m;-T 3608
as explained here http://www.mydatabasesupport.com/forums/ms-sqlserver/348593-cannot-move-msdb-model-even-t3608.html.

2. For me, it didn't work detach/attach master. Instead, I changed the Startup Parametersfor SQL server to point to the new locations of the files. I happened to have left master for the end. I don't know if it would work in different order.

Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.