Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Feb 6 '13 at 19:00

This question came from our site for professional and enthusiast programmers.

up vote 54 down vote accepted

That is expected and documented behavior:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

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.-

USE master;
DROP DATABASE [databasename] ;
share|improve this answer
It's working marc, thanks – srk Feb 6 '13 at 17:10
and notice that backup files will be kept – Junior M Sep 27 '14 at 21:56

protected by Community Aug 7 '15 at 14:57

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.