Sunday, March 11, 2012

Detaching and moving databases

Not sure if this is the exact forum I need but here goes.

Having a bit of an issue 'moving' a database. I've only just installed SQL Server 2005 on a Development/Test box so I can learn and mess around with it without doing any serious damage to anything important.

One of the first things I've done is install the AdventureWorks database and, having installed it, decided I would move the files to their optimum locations. However having detached the database I can't seem to move or copy the .mdf or .ldf files - something still seems to have its tentacles around them - 'Access denied' error. Even rebooting the server doesn't work. So I've had to delete then re-attach the database. The question then is does Detach serve any useful purpose? Or am am doing something wrong? With SS2000 I could detach a database, relocate the physical files and re-attach in seconds. The same procedure with SS2005 has taken me an hour!

Regards,

Gordon F.Do you have any anti-virus or anti-spyware tools installed on this test box?|||

When you detached the database (assuming Management Studio here) did you check the "Drop Connections" box? Without that, connections are not forced to drop, and you may have lingering connections to the database. I'm not sure why rebooting wouldn't resolve that though.

I just went through the whole sequence on my SQL 2005 instance, and it works as expected - no delays.

|||Also check the ACL on the file itself. when I had this issue I gave explicit permissions to the Service account on the mdf and ldf files and then I could copy the files.

No comments:

Post a Comment