EF5: Cannot attach the file ‘{0}' as database '{1}'

Question

I'm encountering the exact issue as described here (read section "Cannot Attach to Deleted MDF File"), but the solution to the problem is not told there...

In short the issue is that after deleting the .mdf file, the following exception is thrown when I try to access the DB using EF 5.0.

DataException->EntityException->SqlException:
Cannot attach the file ‘{0}' as database '{1}'

I did delete the DB file and now I get that nasty error message when running the application expecting it to use it's initializer. Any way to fix this?

Solution 1

If you delete the DB file, it still stays registered with SqlLocalDB. Sometimes it fixes it to delete the DB. You can do this from the command line.

1. Open the "Developer Command Propmpt for VisualStudio" under your start/programs menu.

2. Run the following commands:

   sqllocaldb.exe stop v11.0

   sqllocaldb.exe delete v11.0

Solution 2

For those still searching for a solution...

Go to View / SQL Server Object Explorer and delete the database from the (localdb)\v11.0 subnode!

There is essentially a local database retained of where the files should be, and if you want to delete the database files make sure you remove them from this explorer utility, not manually.

Solution 3

I did try JSobell's solution first but did not see my database listed there. I ran CodingWithSpike's commands from VS Developer Command Prompt, but that did not work either. Finally I ran CodingWithSpike's same commands from Package Manager Console and that worked.

    PM> sqllocaldb.exe stop v11.0
    LocalDB instance "v11.0" stopped.

    PM> 
    PM> sqllocaldb.exe delete v11.0
    LocalDB instance "v11.0" deleted.

    PM> sqllocaldb.exe start v11.0
    LocalDB instance "v11.0" started.

Solution 4

Alright.

My solution was simple, I changed to use local server:

I changed the DataSource attribute in the connection string from:

Data Source=(LocalDb)\v11.0;blah

To:

Data Source=.\SQLEXPRESS;blah

Another solution is login to LocalDb via SQL Management Studio, and try to delete that database:

However it didn't work for me, when I try to delete it it says "TITLE: Microsoft SQL Server Management Studio

The Database '{0}' does not exist on the server. (SqlManagerUI)

When I try to detach it the database doesn't appear in the list for detach selection, "Take offline" also takes me to the error above.

Which leads me to think this is a solid bug in LocalDB.

Solution 5

The easiest fix is to simply change the name of your DB in the connection string. See Rowan Millers blog How to Drop a Database from Visual Studio 2012 for alternate solutions. We hope to fix this problem in a future edition.

View more solutions

Comments

[Nikos]

I have problems trying to use local DB with EF5

[Shimmy Weitzhandler]

Did you experience the same issue and your answer solved it? Which I then accept your post as the answer.

[CodingWithSpike]

yes, I had the same error message after deleting or moving the db file that SqlLocalDB uses.

[Shimmy Weitzhandler]

I didn't test it, but this should probably not work. because DataDirectory is already set to the local App_Data by default, so unless explicitly changed before, your code does nothing.

[workabyte]

this bit me once, the second time was that i had auto migrations == false FML

[RickAndMSFT]

IF you go with this approach, don't change the directory, change the MDF file name. ALso, see romiller.com/2013/05/17/…

[newman]

I didn't find Sql Server Object Explorer, I guess because I'm using VS 2012 Express. So, I can't try out the accepted answer. However, your answer worked for me perfectly. Thanks.

[Santux]

Your answer just saved me. I didn't have the (localdb)\v11.0 subnode from the accepted answer and I couldn't remove the references from the SQL Management Studio, so hurray for you!

[Bart Verkoeijen]

This worked for me after deleting the files. You can also run the commands from Tools --> Library Package Manager --> Package Manager Console.

[Bart Verkoeijen]

LocalDB has as benefit that you can run it under your local account without the need of Administrator priveliges, just like IIS Express. This allows to share projects with many devs without setting up IIS or SQL Server for each individual user.

[Joe]

Fantastic. Run from PM Console and works with both EF5 MVC4 and EF6 MVC5.

[julealgon]

This command shows more databases than the view in the accepted answer does to me. It was the only solution in my case.

[lordcheeto]

Runs from the normal cmd as well; it's added to the PATH (for VS2013, at least).

[kingfleur]

Also, if you don't see it. Just add a new connection and enter "(localdb)\v11.0" (with Windows Auth). It may seem simple, but I was staring at my screen for a while there. :)

[nVentimiglia]

@Peter Ok, Added the database and now I see my 2 contexts. When I delete from this view I get a new error : 'can not retrieve data access level for this database'. I was able to get around this by using the command prompt.

[CodingWithSpike]

As a side-note, the v11.0 is specific to SQL LocalDB 2012. If you are using LocalDB 2014, MS renamed it to MSSqlLocalDb instead.

[Jim Yarbro]

When using EF6's default connection factory, you may be using mssqllocaldb instead. Change the server to (LocalDb)\mssqllocaldb and see if that connects. There's several versions of LocalDb, and one does not show you all.

[StefanG]

Also, what I experienced: Click on disconnect and then connect again if you don't see your database anymore. (Refresh does not help)

[Deviney]

in relation to the above comment you can also not enter any db name and it will just apply to your default one, I used below with vs2015 and it worked: sqllocaldb.exe stop sqllocaldb.exe delete

[Savail]

In VS 2015 I had to type 2 lines like @Deviney but with a '.' at the end of each command: sqllocaldb.exe stop . and sqlolocaldb.exe delete .

[Matt West]

Worked for me! Fantastic, I'd uninstalled and reinstalled VS2015, tried to manually connect a localdb instance for an hour... and this was the solution.

[iAM]

Helpful side note, I encounter this working on an old project with VS 2015 More info. There are also similar changes in VS 2013 from v11.0 to ProjectsV12 . At least on my machine :-).

[Skillzore]

This does not solve the issue for me. None of the solutions do. I have the exact same error, using VS2017 and EF 6.1.3.

[Shimmy Weitzhandler]

What if I have other DBs that I don't want to delete? Isn't there a way to physically delete the mdf file that is out there in the file system?

[CodingWithSpike]

For those that come across this all these years later; v11.0 was the instance name used by "SqlLocalDB" back then. This may have changed or be dependent on your version. You should be able to use sqllocaldb.exe info to find the instance name(s).

[CodingWithSpike]

@Shimmy if you read the original question asked, deleting the .mdf file manually is what seems to cause this issue in the first place. JSobell's answer may work better for you if you want to delete 1 DB from an instance. (in my case, I couldn't get anything to show up in the GUI to delete)

[Shimmy Weitzhandler]

I didn't delete the mdf file. I deleted it via Sql Server Object Explorer.