I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error
cannot create file C:\Program Files…..[databasename].mdf because it already exists
Here's my query to force drop the database
Use master;
ALTER database [databasename] set offline with ROLLBACK IMMEDIATE;
DROP database [databasename];
I understood that, the above query is dropping the database, but it's not deleting the .ldf
and .mdf
files. How to drop the database thoroughly?
A normal query
Drop database [databasename] ; //deletes the database completely, including the ldf and mdf's.
How to force drop a database, which also deletes the .mdf
and .ldf
files?
Best Answer
That is expected and documented behavior:
So why are you taking your database offline first? Just set it into
SINGLE_USER
mode and then drop it as documented on SQL Server Books Online.-Note, database backups will not be deleted as part of the process documented above.