How to force drop database in SQL Server 2008
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
.ldfand.mdffiles. 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
.mdfand.ldffiles?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_USERmode and then drop it as documented on SQL Server Books Online.-USE master; ALTER DATABASE [databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [databasename] ;Note, database backups will not be deleted as part of the process documented above.
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
