[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to move SQL 2005 data and log files

Posted on 2008-11-05
32
Medium Priority
?
1,013 Views
Last Modified: 2011-10-19
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.
0
Comment
Question by:drotkopf
  • 20
  • 12
32 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22889646
howto move databases:
support.microsoft.com/kb/224071
0
 

Author Comment

by:drotkopf
ID: 22891798
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
 

Author Comment

by:drotkopf
ID: 22891908
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:drotkopf
ID: 22891912
Is there a clean way to just uninstall everything?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22892690
>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
 

Author Comment

by:drotkopf
ID: 22894623
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
 

Author Comment

by:drotkopf
ID: 22894655
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
 

Author Comment

by:drotkopf
ID: 22894706
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
 

Author Comment

by:drotkopf
ID: 22897941
help...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22897994
> 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
 

Author Comment

by:drotkopf
ID: 22898039
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22898165
-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
 

Author Comment

by:drotkopf
ID: 22898382
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
 

Author Comment

by:drotkopf
ID: 22899626
still need help... please...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22899715
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
 

Author Comment

by:drotkopf
ID: 22899746
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22899834
do you have maybe 2 sql server services running?
how do you start the sql server?
0
 

Author Comment

by:drotkopf
ID: 22900217
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22900313
>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
 

Author Comment

by:drotkopf
ID: 22900404
See Services Panel
untitled.JPG
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22900543
that's not what I asked for, actually...
0
 

Author Comment

by:drotkopf
ID: 22900682
Sorry. None.
untitled.JPG
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22900742
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
 

Author Comment

by:drotkopf
ID: 22900822
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22900890
(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
 

Author Comment

by:drotkopf
ID: 22900910
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
 

Author Comment

by:drotkopf
ID: 22901620
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
 

Author Comment

by:drotkopf
ID: 22901800
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22902479
>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
 

Author Comment

by:drotkopf
ID: 22903194
Thanks!!
0
 

Author Comment

by:drotkopf
ID: 22934196
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 22934269
>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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question