Relocate LDF files in SQL 2005

I thought I've done this before...fairly easily, but it seems I'm missing a step I may have forgotten. I have several DBs that need to have ONLY their corresponding LDF (log) files relocated to a different drive. I thought the procedure was simply:

1. Rt-click on DB -> Tasks -> Detach
2. Move the LDF file to the proper drive
3. Rt-click on DATABASES heading -> Attach
4. Click the 'Add' button, select the DB and re-attach.

..but alas, I am getting an error. An 'access denied' error. I shouldn't because I'm logged on as Domain Admin. I have privileges that I need. I didn't stop any SQL services because...well...it's during the day and I can't. My assumption is that is what I need to do, but I didn't think I needed to do that to simply detach/attach a DB. I can't move the LDF file, but I can copy it (get the access denied error). But, I need to move it (simply move/paste operation in Windows). Well, I did try and see if the copy alone will work. So, I have a copy in the location I want it, then tried to re-attach the DB and I get a long error that, in the middle of it, says access denied. Any idea what I'm doing wrong here?

Thanks..

~coolsport00
LVL 40
coolsport00Asked:
Who is Participating?
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.

zombieoooCommented:
The SQL service account does not have read/write permission to the directory to access the transaction log files.  Check the account the service is running as, and give the user permission to the directory you have moved your .ldf files.
0
Scott PletcherSenior DBACommented:
Yeah, as a ^&*(^% security "feature", MS added this, to change the file permissions when the file is detached.

Luckily there's a better way to move files now: ALTER the location of the file(s), set the db OFFLINE, copy the file(s) to the new location, then ALTER the db ONLINE.
0
Scott PletcherSenior DBACommented:
Anyway, to your current problem, you have to figure out which id now owns the file, then log in as that id to control the file.  That is, somehow make sure the id using the file is the one that now owns it.

You could also try changing the permissions on the file, but that can be a pain to make work too.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

coolsport00Author Commented:
Thanks for the replies. To answer...
We have an AD acct with local admin priv's for this DB server, that is also the service acct for SQL Server service. It also has access to our 'data' (E:) directory. It didn't have access to the directory I want to move the log to (L:), but adding access to L: didn't work either.

A bit more info...it seems the end user who was doing this procedure, connected to the DB server via Mgmt Studio from his local workstation, then detached the DB, instead of doing it directly on the DB server. So, I thought maybe that had something to do with the 'access denied' msgs? I added this user with Full access on both E: and L:, but again that didn't work. My only other thought is that I'll just simply need to stop the SQL Server services and do this operation?

~coolsport00
0
Scott PletcherSenior DBACommented:
Stopping SQL Server won't affect the file security.  The file is aleady detached from SQL, so it's not SQL affecting it.

It's the *&(^!@ permissions.  I've had fits at various times with that too.  Once I *had* to go back to the person that originally ran the detach and have them attach it.  Then I used the OFFLINE / ONLINE method ... much nicer!
0
Scott PletcherSenior DBACommented:
If the original files are in place, and the detach-er can reattach, I'd have him/her do that.
0
coolsport00Author Commented:
Well, again, it's not just moving the file that's the issue, it's also trying to reattach it (even without the file move). That may be it, too...let me check.
0
coolsport00Author Commented:
Ok...so I went to the user and had him reattach...yes!..that did work. So, I then again now have the DB back. So, I now went to the DB, rt-clicked on it, took it offline, then MOVED the LDF to the L: drive. I went to put the DB back online, and I got some other error :( (ugh) See error below:

"Unable to execute requested command.
------------------------------
Attempt to retrieve data for object failed for Server 'DBserver\Instance'.  (Microsoft.SqlServer.Smo)

The Database '[TEST]' does not exist on the server. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.5292.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476"

So, I then moved the LDF file back to E: and the same error above occurred. Just FYI...I am by no means a DB person at all. So, sorry for the "neophyte-ness" :)

~coolsport00
0
Scott PletcherSenior DBACommented:
Ouch.  Sorry, yeah, wish you had checked back with me before taking it offline.
"
Luckily there's a better way to move files now: ALTER the location of the file(s), set the db OFFLINE, copy the file(s) to the new location, then ALTER the db ONLINE.
"
See the "ALTER the location of the file(s), [then] set the db OFFLINE".

That's a *vital* step.

Move the log back to its original location and set the db online.

Issue the command to tell SQL about the new log file location.

First, get the log *logical* name from the first column below:
EXEC [TEST].dbo.sp_helpfile

The *first* column has the *logical* file name, find the name for the log file.

Then issue this command:

ALTER DATABASE [TEST] MODIFY FILE ( NAME = logical_file_name_from_above, FILENAME = 'x:\new\drive\and\path\for\log\file\logfile.ldf' )

NOW you can:
USE master
ALTER DATABASE [TEST] SET OFFLINE

--go to Windows and move the log file

ALTER DATABASE [TEST] SET ONLINE
0
coolsport00Author Commented:
Ah, ok...sorry about that. If you're talking in SQL Query code, I'm lost. So...as of now...with this error, etc. how do I resolve this? And do so via GUI (I can try SQL Query if need be, but again...this is in prod, and I don't wanna break anything).

Thanks for your help!
0
Scott PletcherSenior DBACommented:
I *always* use Query commands, as shown, in "New Query...", rather than the GUI for db maintenance tasks.  Otherwise when it's done you can't be *sure* exactly what you typed in / selected.

Honestly, I've never used the GUI to do this kind of thing, so I can't help you there right now.

Maybe someone else can help?? ... If someone else is actually keeping up with this thread :-)
0
Scott PletcherSenior DBACommented:
REWRITE:
I *always* use Query commands, the way I've shown them above, entered via hitting "New Query..." and then typing in the commands, rather than ...
0
coolsport00Author Commented:
Ok..think I fixed it (I hope). So, for some reason, the DB showed up with the funky red arrow icon pointing downard and (OFFLINE) at the end of the DB name. I refreshed my DB window and it still showed up as offline. I logged out of Mgmt Studio, then got back in and the DB then didn't show up in the tree of DBs. So I was like, 'ah, ok'. So, I moved the LDF file to L:, then rt-clicked on DATABASES and selected 'Attach'. I was then able to attach the DB and browse to the L: drive for the log and it seems to have worked. My question...why did the the DB still show up after I took it offline? I assumed it is supposed to, but once I moved the LDF while it was offline, that's when the issue happened?

So, in moving forward, how to I seamlessly have this end user move LDFs to L: drive, with no issues?

Thanks again.
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
coolsport00Author Commented:
And, I may need to open another question so I can get a GUI-based answer? :)
0
Scott PletcherSenior DBACommented:
Yes, that would be best ... sorry.
0
Scott PletcherSenior DBACommented:
I don't trust SSMS to do that kind of thing thru the GUI anyway.  I've had people tell me that every once in a while it "freezes" on the action, although it apparently usually does it in the background, the screen never completes.
0
coolsport00Author Commented:
I ended up figuring out answer myself. Thanks to the others for their support.
0
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.

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.