Sunday morning (I love maintenance windows) I had to move some datafiles that were placed incorrectly. I thought this would be an easy job. Detach, move the files and reattach, right ? Wrong.
It appears that SQL Server won't release handles on the datafiles until you put the databases in offline mode. However when you reattach a database that you previously put offline and detached you get an error message (it will attach the database though) and the database is in read-only mode.
Even worse for me was that I was able to move datafiles of an offline database that I hadn't detached (it was 2am I guess I wasn't so clear). This resulted in weird behaviour namely OS errors when trying to reattach the files. I was already getting visions of restoring the lastest backup and all transaction logs, but my last attempt at recovering the databases worked.
I finally ended up moving all the files back, putting the database in read-only mode (to prevent missing any changes to the data), creating a backup and restoring the backup with MOVE TO parameters and then turning off read-only mode, which is actually much easier and faster to do.