SQL LocalDB - Cannot delete a DB when its files ar

Question

How can I delete a SQL LocalDB database that has had its files delete?

Dropping the database yields this message:

Unable to open the physical file "C:\Users\Public\Documents\LocalDB.Tests.3d0d7339-7cf2-45fe-a83b-b5079112ab80.mdf". Operating system error 2: "2(The system cannot find the file specified.)".

File activation failure. The physical file name "C:\Users\Public\Documents\LocalDB.Tests.3d0d7339-7cf2-45fe-a83b-b5079112ab80_log.ldf" may be incorrect.

Running master.sp_databases actually doesn't show them, but the Management Studio does.

Answer 1

The problem is that you are trying to drop a database when the physical file has been deleted or couldn't be found.

To get around this you can detach the database. Detaching will drop the database without attempting to remove the file from the filesystem.

EXEC sp_detach_db 'My_Db'

Answer 2

I'm assuming you're using (localdb) bundled with SQL Server 2012.

If you're using SQL Server 2014, use (localdb)\MSSQLLocalDB in place of (localdb)\v11.0 below

• Open a command prompt
• Start the localDb instance if it is not already running: “C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqllocaldb.exe” start “v11.0″
• Drop the localDb database by running the following command: “C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S (localdb)\v11.0 -E -d master -Q “DROP DATABASE [myDatabase]”
• You can stop the localDb service now: “C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqllocaldb.exe” stop “v11.0″

Source: http://kazimnami.azurewebsites.net/techblog/2013/02/27/delete-localdb-database-after-physical-files-have-been-deleted/