Deleting and recreating database with same name

Question...
I created a database, but I want to totally blow that database away
and recreate it. I right-clicked on the DB and deleted it, no longer
there. Now when I go to recreate the database with the same name, I
get the following error:
Error 5170: Cannot create file 'C:\...MDF' because it already exists.
CREATE DATABASE failed. Some file names listed could not be created.
Check previous errors.
Okay, when I tell SQL I want to delete a database, I want it 100%
deleted. Apparently SQL's idea of Delete and mine are different, so
any suggestions on how I can totally remove this database? Since MS
is known to do plenty hidden the background, I don't want to just
delete the .MDF file, since I'm sure this will confuse SQL quite a
bit.
So back to the root question... how do I 100% delete a database where
I can create another DB with the same name?
Thanks,
Alex.You'll need to delete the MDF file it's complaining about from the OS.
HTH
Ryan Waight, MCDBA, MCSE
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.|||> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted.
This is what DROP DATABASE does, which is the command called from QA and EM. Unless you detach it,
of course.
> Apparently SQL's idea of Delete and mine are different
No, something strange must have happened.
Just try to delete the database files. If the database still exists in the eyes of SQL Server, then
the files are locked and you can't delete them.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.