How to delete (localdb) database if the file is gone

Question

If I run SQL Server Management Studio, and tell it to connect to (localdb)\v11.0, it knows about every database I've ever used, despite the fact that most of the the database files are long gone.

If I ask it to delete one of these databases, it complains that it can't DROP the database because the database file is gone (duhhh). So, how do I clean up this mess and delete all of database references whose assicated database files are gone?

Bob

Damned

Answer 1

In this situation, detach the database rather than trying to drop it. In SQL Management Studio, right-click on the database, select "Tasks" then "Detach".

Sarmentum

Weird bug in SSMS 2012 when you try to do this; If you tried to Delete a DB and got the error message mentioned originally, then the "Detach" window will no longer work. You need to disconnect and reconnect to the DB server to make it work again. – Essayistic

The database doesn't show up in the detach list for me but it's still listed in the database server (2016) – Swainson

Same here for me as for Damien, trying to detach, nothing in the detach list and I get told "nope can't detach unless you select a file to detach" – Donegan

Answer 2

All you need to do is to recreate the instance, tested with SQL 2012 ! just go in command prompt with admin rights and type:

//list the instancies
sqllocaldb i

//stop selected instance
sqllocaldb p "selected instance"

//delete
sqllocaldb d "selected instance"

//recreate or create new one 
sqllocaldb c "new instance"

Leisure

If you database is used you have to stop it with next command: sqllocaldb stop "selected instance". – Apopemptic

short for "stop" is just "p" – Leisure

Answer 3

I had the same problem. When designing DB using code first, I simply remove old DBs. It ends up with multiple deleted DB appearing in SQL Server Management Studio. Then when I try to query the DB, it becomes difficult to find the correct DB instance from amongst the deleted.

As IRM suggested, I tried to Detach those deleted DBs, and for some of them it works great!

However still I have several left. Then I tried "Take offline" on those DBs. Each time when I tried to take DB offline, the SQL Server Management Studio crashed. After SQL Server Management Studio restarted, the DB was gone. So try to do "take offline" if detach and delete don't work for you.

Anacoluthia

Fun fact: 3 years later, Management Studio 2014 still crashes when doing this! – Fourfold

Answer 4

I'd collected hundreds of these, and detaching them individually was just too damned tedious.

What I did:

SELECT 'EXEC sp_detach_db ''' + name + ''''
FROM sys.databases
;

This gave me a list of exec commands:

EXEC sp_detach_db 'E:\...\ADATABASE.MDF'
EXEC sp_detach_db 'E:\...\ANOTHERDATABASE.MDF'
EXEC sp_detach_db 'E:\...\ATHIRDDATABASE.MDF'
....
EXEC sp_detach_db 'master'
EXEC sp_detach_db 'model'
EXEC sp_detach_db 'msdb'
EXEC sp_detach_db 'tempdb'

Copy the results back into the command window, highlight everything other than the system databases, and execute.

Melodee

Answer 5

The accepted answer here says to detach your server, however if you have snapshots you can't detach them, and you run into the same issue deleting them.

You can delete them, however, by creating empty files in the location where sql server thinks the files should be, restarting the sql server service and then trying again.

If you are unsure how to find the physical location, you might find it here: https://dba.stackexchange.com/questions/49811/location-of-the-mdf-file-of-the-database#:~:text=5%20Answers&text=There%20are%20few%20ways%20to,the%20Path%20and%20FileName%20columns.

Besprinkle