Unanswered: sharing violation while using copy database wizard
I'm trying to move databases to another sql server but I always get sharing violation error message while the wizard tries to copy the .mdf file from the source-server. Any suggestions (besides rebooting to solve this issue?
I have also tried to copy the file within the source server, but I still get the same message.
If SQLAgent is using the database in question, then your SP_DETACH will not work either. In this case you either have to investigate what process controlled by SQLAgent is interfering with your command, or stop SQLAgent all together and be done with it.
I have, but in epo-x's case, apparently, there is a process that has this database connected to, so was the reason for my recommendation. Also, if you look at the log that copy database wizard produces, then you'll see that it does SP_DETACH, OS-based copy, SP_ATTACH on the source, and SP_ATTACH on the target. In a sense, what you suggest is absolutely right, because this is the sequence of events that the wizard follows as well
There is also a shorter way to see if the wizard (or your suggestion) will work or not:
sp_dboption 'database_name', 'off', true
If the last command does not result in error, then you can either fire the wizard, or perform the copy of the files manually, because the database is set OFF-LINE which is the same as detach except sysdatabases entry is still there with a different status.