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?

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. — OdeToCode, Nov 09 '12 at 13:06
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. — Shimmy Weitzhandler, Nov 10 '12 at 16:36
From the EF expert Rowan Miller - see http://romiller.com/2013/05/17/how-to-drop-a-database-from-visual-studio-2012/ We hope to provider better solutions in the next VS version. — RickAndMSFT, Jul 01 '13 at 21:06
A useful troubleshooting guide: http://odetocode.com/blogs/scott/archive/2012/08/15/a-troubleshooting-guide-for-entity-framework-connections-amp-migrations.aspx — Tim Abell, Jul 29 '14 at 09:24

score 223 · Accepted Answer · edited May 03 '13 at 09:09

223

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.

Open the "Developer Command Propmpt for VisualStudio" under your start/programs menu.
Run the following commands:

sqllocaldb.exe stop v11.0

sqllocaldb.exe delete v11.0

edited May 03 '13 at 09:09

Shimmy Weitzhandler

101,809
122
424
632

answered May 02 '13 at 13:22

CodingWithSpike

42,906
18
101
138

Did you experience the same issue and your answer solved it? Which I then accept your post as the answer. – Shimmy Weitzhandler May 03 '13 at 09:11
yes, I had the same error message after deleting or moving the db file that SqlLocalDB uses. – CodingWithSpike May 03 '13 at 15:15
1

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. – newman Jul 06 '13 at 22:42
3

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! – Santux Jul 31 '13 at 11:33
18

This worked for me after deleting the files. You can also run the commands from Tools --> Library Package Manager --> Package Manager Console. – Bart Verkoeijen Nov 17 '13 at 10:00
Fantastic. Run from PM Console and works with both EF5 MVC4 and EF6 MVC5. – Joe Feb 21 '14 at 02:37
This command shows more databases than the view in the accepted answer does to me. It was the only solution in my case. – julealgon Apr 30 '14 at 16:37
Runs from the normal cmd as well; it's added to the PATH (for VS2013, at least). – lordcheeto May 07 '14 at 06:41
12

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
4

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 – Deviney Oct 12 '15 at 11:46
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 . – Savail Jun 03 '16 at 11:21
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. – Matt West Dec 08 '16 at 19:33
1

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. – Skillzore Aug 10 '17 at 13:17
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? – Shimmy Weitzhandler Nov 30 '17 at 17:29
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 Dec 04 '17 at 15:48
@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) – CodingWithSpike Dec 04 '17 at 15:49
I didn't delete the _mdf_ file. I deleted it via _Sql Server Object Explorer_. – Shimmy Weitzhandler Dec 05 '17 at 01:07

score 148 · Answer 2 · edited Nov 30 '17 at 17:29

148

For those still searching for a solution...

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

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.

edited Nov 30 '17 at 17:29

Shimmy Weitzhandler

101,809
122
424
632

answered Apr 05 '13 at 11:00

JSobell

1,825
1
14
10

1

this bit me once, the second time was that i had auto migrations == false FML – workabyte Jun 29 '13 at 00:26
8

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. :) – Peter Jun 06 '14 at 14:34
@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. – nVentimiglia Jun 07 '14 at 17:50
1

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. – Jim Yarbro Feb 06 '15 at 19:21
Also, what I experienced: Click on disconnect and then connect again if you don't see your database anymore. (Refresh does not help) – StefanG Feb 09 '15 at 11:08

score 19 · Answer 3 · answered Jun 25 '14 at 20:42

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.

Shimmy Weitzhandler · Answer 4 · 2015-03-14T22:58:51.663

9

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:

enter image description here

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.

edited Mar 14 '15 at 22:58

answered Nov 09 '12 at 08:17

Shimmy Weitzhandler

101,809
122
424
632

I have problems trying to use local DB with EF5 – Nikos Feb 20 '13 at 17:03
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. – Bart Verkoeijen Nov 17 '13 at 10:03

score 4 · Answer 5 · answered Sep 30 '13 at 17:53

4

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.

answered Sep 30 '13 at 17:53

RickAndMSFT

20,912
8
60
78

score 2 · Answer 6 · answered Jul 17 '13 at 12:53

2

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

answered Jul 17 '13 at 12:53

gifted

21
1

score 1 · Answer 7 · answered Nov 27 '15 at 05:46

1

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

answered Nov 27 '15 at 05:46

Ulugbek

11
2

The One · Answer 8 · 2017-03-22T14:57:28.180

1

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

edited Mar 22 '17 at 14:57

answered Mar 22 '17 at 14:52

The One

4,560
5
36
52

Helpful side note, I encounter this working on an old project with VS 2015 [More info](http://thebillwagner.com/Blog/Item/2015-02-05-VersionindependentlocalDBinVisualStudio2015). There are also similar changes in VS 2013 from `v11.0` to `ProjectsV12` . At least on my machine :-). – iAM Apr 26 '17 at 07:50

score 1 · Answer 9 · edited Jul 11 '18 at 18:10

1

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

edited Jul 11 '18 at 18:10

Mittal Patel

2,732
14
23

answered Jul 11 '18 at 16:32

Tony Newsom

11
1

score 0 · Answer 10 · answered May 22 '13 at 16:25

0

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" />

answered May 22 '13 at 16:25

Filipe Scur

133
1
9

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. – Shimmy Weitzhandler May 23 '13 at 04:55
IF you go with this approach, don't change the directory, change the MDF file name. ALso, see http://romiller.com/2013/05/17/how-to-drop-a-database-from-visual-studio-2012/ – RickAndMSFT Jul 01 '13 at 21:07

score 0 · Answer 11 · answered Mar 27 '14 at 19:19

0

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

answered Mar 27 '14 at 19:19

Edgar Froes

768
8
20

score 0 · Answer 12 · answered Jun 28 '14 at 19:03

0

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

answered Jun 28 '14 at 19:03

Nidhin

166
3
8

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

12 Answers12

Linked