How to move SQL 2005 data and log files

howto move databases:
support.microsoft.com/kb/224071

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?

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

Is there a clean way to just uninstall everything?

>it doesn't start anymore.
what do you get in the errorlog file?
the path of that file is indicated by the startup parameters...

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.

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!

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.

help...

> 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?

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!

-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?

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

still need help... please...

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").

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.

do you have maybe 2 sql server services running?
how do you start the sql server?

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

>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?

See Services Panel
untitled.JPG

that's not what I asked for, actually...

Sorry. None.
untitled.JPG

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?)

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.

(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?

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?

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

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!

>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".

Thanks!!

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!