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

问题:

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

回答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".

回答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"

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

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