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?

Heist

Sorry I didn't provide a specific answer, but at the the time I didn't have a solution that works 100% of the time. I try to avoid the error now by not using user attached databases. I generally could fix the error by connecting with SQL Server Management Studio and detaching the database in the error. – Leonor

The problem is the database doesn't exist anymore as I already deleted the physical file. I actually tried to look after it in one of the studios (integrated in VS and external). My answer really isn't a solution but rather a workaround, it just tells that you don't have to stick to LocalDb. – Heist

From the EF expert Rowan Miller - see romiller.com/2013/05/17/… We hope to provider better solutions in the next VS version. – Inutility

A useful troubleshooting guide: odetocode.com/blogs/scott/archive/2012/08/15/… – Wimsatt

Answer 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

Kitti

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

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

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. – Dunaj

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! – Krein

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

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

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

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

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. – Kitti

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 – Zetana

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

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. – Detumescence

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. – Maggee

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? – Heist

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). – Kitti

@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) – Kitti

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

Answer 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.

Typescript

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

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. :) – Skew

@Skew 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. – Braithwaite

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. – Van

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

Answer 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.

Thilde

Answer 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.

Heist

I have problems trying to use local DB with EF5 – Olympic

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. – Celanese

Answer 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.

Inutility

Answer 6

The best and easy answer I just solved it now, Just use ur sql server name as data source, initial catalog be your database name and there you go remove the mdf line

Disembarrass

Answer 7

In my case I use migrations and in config i simply changed name of dataContext and dataContext class itself (just rename), then try again and that helped

Enlighten

Answer 8

For SQL 2014 please follow CodingWithSpike selected answer and this comment

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. – CodingWithSpike Aug 29 '14 at 19:20

Pfeifer

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 :-). – Embus

Answer 9

I had the same problem. I ran the following commands in the package manager console and it fixed the problem

sqllocaldb.exe stop MSSqlLocalDb

sqllocaldb.exe delete MSSqlLocalDb

Damian

Answer 10

I had the same problem and I solved it by manually setting the "DataDirectory" folder to another folder in my app binaries.

I put this line in the Global.asax Application_Start method:

AppDomain.CurrentDomain.SetData("DataDirectory", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "App_Data"));

My connection string is currently set to this:

<connectionStrings>
    <add name="DataContext" connectionString="Data Source=(LocalDb)\v11.0; Initial Catalog=DataContext; Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|DataContext.mdf"
  providerName="System.Data.SqlClient" />

Garfield

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. – Heist

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

Answer 11

I could fix it by renaming DataBase name in my connection String, from the default aspnet-{numbers} to a simple name, it worked.

Bichromate

Answer 12

Connect to (LocalDb)\v11.0 using Sql server management studio, delete the db and then do an update-database in package manager console.

Bohaty