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.

Design: bit on or off?

I'm asking lots of design questions here.. but they are little ones,
sometimes matter of taste, sometimes more than that.
Imagine I have a table of "Keys". Some of these keys will be "active" and
some will be "blocked".
I'm trying to decide whether I should use a BIT column and call this
"active" or "blocked".
So should bit 1 mean "active" or mean "blocked" ? There will almost
certainly be more active than blocked keys. Which one is more intuitive or
likely convenient in practice?
Of course I could use a set "yes/no" or "active/disabled" but for only 2
possibilities, a bit seems more efficient and convenient in front en back
end. What are your recommendations/tastes?
LisaHi, Lisa
If you use a bit column, 1 should represent true and 0 should represent
false. So, if the column name is "active", 1 means that the key is
active, 0 means that it's blocked.
However, you should consider using a char(1) column with a constraint
like "Status IN ('A','B')", because it is possible that sometime in the
future you may want another status value, for example "pending". If you
use a codification on a char(1), make sure that it's meaning is well
documented (for example in the Description of the column, if you use
Enterprise Manager).
Razvan|||If "active" basically means "enabled" or "on" or "true", then use 1 and 0
for "blocked".
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:u5kVzK09FHA.4004@.TK2MSFTNGP14.phx.gbl...
> I'm asking lots of design questions here.. but they are little ones,
> sometimes matter of taste, sometimes more than that.
> Imagine I have a table of "Keys". Some of these keys will be "active" and
> some will be "blocked".
> I'm trying to decide whether I should use a BIT column and call this
> "active" or "blocked".
> So should bit 1 mean "active" or mean "blocked" ? There will almost
> certainly be more active than blocked keys. Which one is more intuitive or
> likely convenient in practice?
> Of course I could use a set "yes/no" or "active/disabled" but for only 2
> possibilities, a bit seems more efficient and convenient in front en back
> end. What are your recommendations/tastes?
> Lisa
>|||Lisa Pearlson wrote:
> I'm asking lots of design questions here.. but they are little ones,
> sometimes matter of taste, sometimes more than that.
> Imagine I have a table of "Keys". Some of these keys will be "active" and
> some will be "blocked".
> I'm trying to decide whether I should use a BIT column and call this
> "active" or "blocked".
> So should bit 1 mean "active" or mean "blocked" ? There will almost
> certainly be more active than blocked keys. Which one is more intuitive or
> likely convenient in practice?
> Of course I could use a set "yes/no" or "active/disabled" but for only 2
> possibilities, a bit seems more efficient and convenient in front en back
> end. What are your recommendations/tastes?
> Lisa
I'd prefer to use a CHAR or maybe an INT status code. That way, you can
add more statuses if you need to, you can use a meaningful readable
code that everyone can understand and you avoid some of the peculiar
quirks of the BIT type (for example some numeric operators are valid
for BIT and others aren't).
David Portas
SQL Server MVP
--