<p>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:</p>

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

<p>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.</p>

<p>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.  </p>

<p>How can I find out what is locking the files and unlock them.</p>

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

<p><strong>Update 18/09/2008</strong></p>

<p>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:</p>

<ol>
<li>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".  </li>
<li>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.  </li>
<li>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. </li>
</ol>

<p>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.</p>

<p>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.</p>

[{'answer_id': 78596, 'author': 'Charles Graham', 'author_id': 7705, 'author_profile': 'https://Stackoverflow.com/users/7705', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 78635, 'author': 'Bryant', 'author_id': 10893, 'author_profile': 'https://Stackoverflow.com/users/10893', 'pm_score': 1, 'selected': False, 'text': '<p>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. </p>\n\n<p>Are there any attributes on the file?</p>\n\n<p>Update: If you can\'t copy the files then something must be locking them. I would check out <a href="http://www.emptyloop.com/unlocker/" rel="nofollow noreferrer">Unlocker</a> 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.</p>\n'}, {'answer_id': 78636, 'author': 'tbreffni', 'author_id': 637, 'author_profile': 'https://Stackoverflow.com/users/637', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 78650, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>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. </p>\n\n<p>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.</p>\n'}, {'answer_id': 1141567, 'author': 'onupdatecascade', 'author_id': 139938, 'author_profile': 'https://Stackoverflow.com/users/139938', 'pm_score': 2, 'selected': False, 'text': '<p>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</p>\n'}, {'answer_id': 5528819, 'author': 'Alex from Jitbit', 'author_id': 56621, 'author_profile': 'https://Stackoverflow.com/users/56621', 'pm_score': 0, 'selected': False, 'text': '<p>I\'ve had a similar issue. Nothing seemed to resolve it - even tried to reboot the machine completely, restarting SQL services etc. ProcMon and ProcessExplorer were showing nothing so I figured - the "lock" is done by OS.</p>\n\n<p><strong>I resolved it by DELETING the file and restoring it back from the drive mounted under another drive letter.</strong></p>\n\n<p>PS. My database file was not on a USB drive, but on a TrueCrypt-drive (in some you can say it\'s a "removable drive" as well)</p>\n'}, {'answer_id': 14593498, 'author': 'mattpm', 'author_id': 590021, 'author_profile': 'https://Stackoverflow.com/users/590021', 'pm_score': 0, 'selected': False, 'text': "<p>Within SQL Server Configuration Manager, look in SQL Server Services.  For all your SQL Server instances, look at which account is selected in the Log On Tab - Log On As:.  I've found for instance, changing it to the Local System account resolves the issue you've had.  It was the only thing that actually worked for me - and certainly, no shortage of people have had the same problem.</p>\n"}, {'answer_id': 20780330, 'author': 'sarah', 'author_id': 3135953, 'author_profile': 'https://Stackoverflow.com/users/3135953', 'pm_score': 0, 'selected': False, 'text': '<p>It\'s a security issue on -file level security - you have detached db with different credential and attaching it with other credential - just browse the article <a href="http://www.sqlservermanagementstudio.net/2013/12/troubleshooting-with-attaching-and.html" rel="nofollow">http://www.sqlservermanagementstudio.net/2013/12/troubleshooting-with-attaching-and.html</a> \nAnd try copy pasting it to different location.</p>\n'}, {'answer_id': 47219578, 'author': 'Ali Sheikhpour', 'author_id': 4700922, 'author_profile': 'https://Stackoverflow.com/users/4700922', 'pm_score': 0, 'selected': False, 'text': '<p>I solved similar issue by granting system administrator to all permissions:</p>\n\n<ol>\n<li>right click > properties</li>\n<li>security tab</li>\n<li>in <code>group or usernames</code> click edit.</li>\n<li>click add > advanced</li>\n<li>click <code>find now</code> to list all available permissions.</li>\n<li>choose administrator and add it to list.</li>\n<li>grant it to has full permission.</li>\n</ol>\n'}, {'answer_id': 51802582, 'author': 'manisha', 'author_id': 10213163, 'author_profile': 'https://Stackoverflow.com/users/10213163', 'pm_score': -1, 'selected': False, 'text': '<p>I had the same issue. Someone had detached the files and left, and we were unable to move it to another drive. But after taking ownership of the file (security-->advanced-->take ownership to your login id), and then adding your login id to the security tab and giving access on the file, was able to move.</p>\n'}]
['https://Stackoverflow.com/questions/78560', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/10135/']