Locked SQL Server Data Files

Question

I have an SQL Server database where I have the data and log files stored on an external USB drive. I switch the external drive between my main development machine in my office and my laptop when not in my office. I am trying to use sp_detach_db and sp_attach_db when moving between desktop and laptop machines. I find that this works OK on the desktop - I can detach and reattach the database there no problems. But on the laptop I cannot reattach the database (the database was actually originally created on the laptop and the first detach happened there). When I try to reattach on the laptop I get the following error:

Unable to open the physical file "p:\SQLData\AppManager.mdf". Operating system error 5: "5(error not found)"

I find a lot of references to this error all stating that it is a permissions issue. So I went down this path and made sure that the SQL Server service account has appropriate permissions. I have also created a new database on this same path and been able to succesfully detach and reattach it. So I am confident permissions is not the issue.

Further investigation reveals that I cannot rename, copy or move the data files as Windows thinks they are locked - even when the SQL Server service is stopped. Process Explorer does not show up any process locking the files.

How can I find out what is locking the files and unlock them.

I have verified that the databases do not show up in SSMS - so SQL Server does not still think they exist.

Update 18/09/2008

I have tried all of the suggested answers to date with no success. However trying these suggestions has helped to clarify the situation. I can verify the following:

1. I can successfully detach and reattach the database only when the external drive is attached to the server that a copy of the database is restored to - effectively the server where the database is "created" - lets call this the "Source Server".
2. I can move, copy or rename the data and log files, after detaching the database, while the external drive is still attached to the Source Server.
3. As soon as I move the external drive to another machine the data and log files are "locked", although the 2 tools that I have tried - Process Explorer and Unlocker, both find no locking handles attached to the files.

NB. After detaching the database I tried both stopping the SQL Server service and shutting down the Source Server prior to moving the external drive - still with no success.

So at this stage all that I can do to move data between desktop and laptop is to make a backup of the data onto the external drive, move the external drive, restore the data from the backup. Works OK but takes a bit more time as the database is a reasonable size (1gb). Anyway this is the only choice I have at this stage even though I was trying to avoid having to go down this path.

Answer 1

When you are in Enterprise Manager or SSMS, can you see the name of the database that you are talking about? There might be a leftover database in a funky state. I'd make sure that you have a backup or a copy of the mdf somewhere safe. If this is the case, maybe try dropping the database and then re-attaching it.

Answer 2

Can you copy the files? I'd be curious to know if you can copy the files to your laptop and then attach them there. I would guess it is some kind of permissions error also, but it sounds like you've done the work to fix this.

Are there any attributes on the file?

Update: If you can't copy the files then something must be locking them. I would check out Unlocker which I haven't tried but sounds like a good starting point. You might also try taking ownership of the files under the file permissions.

Answer 3

I would try backing up the database on the desktop, and then see if it will restore successfully on the laptop. Doesn't explain your issue but at least you can move forward.

Answer 4

Run sqlservr.exe in debug mode with the /c switch and see what happens starting up. Any locking or permissions issue can be put to bed by making a copy of the file and transfering the copy to the origional.

Also check the associated log file (.ldf) .. If that file is missing or unavaliable you will not be able to mount the database to any sane/consistant state without resorting to emergency bypass mode.

Answer 5

Any update on this? I am seeing similar issues where the original drive is required or somehow referenced from the database.

Answer 6

Crazy as it sounds, did you try manually granting yourself perms on the files via right-click / properties / security? I think SQL Server 2005 will set permissions on a detached file exclusively to the principal that did the detach (maybe your account, maybe the account under which the SQL Server service runs) and no-one else can manipulate the file. To get around this I have had to manually grant myself file permissions on MDF and LDF files before moving or deleting them. See also blog post at onupdatecascade.com