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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.