Cannot attach the file *.mdf as database

Question

Basically I've followed a tutorial and decided to delete the .mdf file afterwards.

Now whenever I try to run the application I get the following error (the title of this thread). The code where I get the error is shown below (ASP.NET MVC 4):

OdeToFoodDB db = new OdeToFoodDB();

public ActionResult Index()
{
    var model = db.Restaurants.ToList();
    return View(model);
}

My connection string is the following:

<add name="DefaultConnection" 
     connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=OdeToFoodDb;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\OdeToFoodDb.mdf" 
     providerName="System.Data.SqlClient" />

I've tried looking at the SQL Server Object Explorer but it looks the following:

Also, in Server Explorer I don't see any data connections.

And when I try to add a new connection in Server Explorer I don't see any databases named OdeToFoodDb.

Sorry for this wide question but I'm new to Entity Framework and don't quite get what's wrong here.

You say that you deleted the .mdf file. That is the SQL Server database file. If you've deleted it, there is no database, hence the error. — STLDev, Jun 09 '13 at 18:36
Wanted to say the same thing as @STLDeveloper , How can the code continue if it doesn't finds the required DB... — DayTimeCoder, Jun 09 '13 at 18:38
After compiling is there a OdeToFooDb.mdf in the bin\Debug\App_Data folder of your mvc project? — rene, Jun 09 '13 at 18:43
@rene No There is no mdf files at all. How do I generate an mdf file? — John Mayer, Jun 09 '13 at 18:48
@rene There are no mdf file at all. I tried renaming the dbcontext class and now it works. What could have caused the issue? — John Mayer, Jun 10 '13 at 17:01
See my answer below if you want to know how to fix this in SQL SERVER Management Studio. — joedotnot, Jul 12 '16 at 08:14
The question should really say "how do I recreate the mdb from scratch" — rollsch, Dec 22 '16 at 07:42
See the below link https://petermcintyre.com/topics/aspnet-mvc-fix-cannot-attach-the-file-as-database/ — Ragesh P Raju, Jun 04 '19 at 00:15
I had the same situation as the OP: using localdb; ran once; manually deleted the .mdf file afterwards. Renaming the `DbContext` class as the OP suggested worked for me. That involved: (1) changing the original `FooDbContext` class to anything else, such as `XXXDbContext`; (2) in Web.Debug.config, change just the name of the connection string, such as ``. — Mike Finch, Sep 15 '20 at 17:58

score 131 · Answer 1 · answered Nov 24 '13 at 16:05

131

I think that for SQL Server Local Db you shouldn't use the Initial Catalog property. I suggest to use:

<add name="DefaultConnection" 
     connectionString="Data Source=(LocalDb)\v11.0;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\OdeToFoodDb.mdf" 
     providerName="System.Data.SqlClient" />

I think that local db doesn't support multiple database on the same mdf file so specify an initial catalog is not supported (or not well supported and I have some strange errors).

answered Nov 24 '13 at 16:05

Davide Icardi

11,919
8
56
77

18

Removing the "Initial Catalog" portion from the connection string resolved this issue for me. – Tim Friesen Nov 24 '14 at 13:39
2

All the other suggestions were complete overkill. This was simple, terse and worked. – Jammer Jul 10 '15 at 17:16
1

This has noting to do with the real problem. Of course you can specify a Catalog name. – Xan-Kun Clark-Davis Nov 15 '15 at 10:23
This fixed my issue when trying to do a EF update-database. – J4S0Nc Feb 19 '16 at 20:03
This should be the answer. – mariocatch Apr 27 '16 at 14:04
8

This is the actual answer to the problem, or a better workaround at least for this bug. The official answer on this question is wrong. – Nathan McKaskle Aug 10 '16 at 15:52
2

In other words, for localdb you need to replace "initial catalog = ..." with "AttachDBFilename = ...", otherwise you will have random SqlExceptions when trying to open a connection. – seguso Aug 22 '16 at 20:19
Here is the best answer, work for me. I do CI/CD, and use LocalDB for each deployment. – Thach Lockevn Oct 11 '19 at 02:38

Chris · Accepted Answer · 2016-07-14T13:41:28.243

61

Take a look at this: Entity Framework don't create database

I would try giving the database a different name. Sometimes you can run into problems with SQL Express when trying to create a database with the same name a second time. There is a way to fix this using SQL Server Management Studio but it's generally easier to just use a different database name.

Edit This answer was accepted because it confirms the bug and the workaround used by OP (renaming database could help). I totally agree that renaming the database is not really an acceptable way, and does not totally solve the issue. Unfortunatly I didn't check the other ways to really solve it in SSMS.

edited Jul 14 '16 at 13:41

answered Jun 10 '13 at 20:04

Chris

8,527
10
34
51

3

I'd really like to know this fix using SSMS since I'm having the same problem and I'd like to keep the database name intact. – julealgon Apr 30 '14 at 14:47
Yes please @Chris, how do you fix this problem with SSMS? – Ian Campbell Jun 17 '14 at 03:39
@julealgon that process explained [here](http://stackoverflow.com/a/15415154/1539073) – Bogac Aug 27 '14 at 16:14
3

Yo don't need to do that, only delete de database from (and close connections) from visual studio SQL Server Object Explorer View – Sebastián Rojas Oct 02 '15 at 18:54
I don't think that is a good way and absolutely unusable for production systems. – Xan-Kun Clark-Davis Nov 15 '15 at 10:18
See my answer below if you want to know how to fix this in SQL SERVER Management Studio. – joedotnot Jul 12 '16 at 07:33
This did not work for me at all. Changing the name did absolutely nothing. I was following a tutorial as well that instructed us to delete the DB now I get this error. – Nathan McKaskle Aug 10 '16 at 15:50
I would say that https://stackoverflow.com/questions/15403469/how-can-i-install-the-northwind-database-into-sql-server-local-db/15415154#15415154 is the better explanation for what you need to do. – Matt Vukomanovic Jan 24 '18 at 01:24
Renaming should not be the appropriate convention but hey, I needed this to work now – kabuto178 Mar 11 '19 at 21:10

score 37 · Answer 3 · edited Jan 23 '15 at 14:52

37

From Package Manager Console run:

sqllocaldb.exe stop v11.0

sqllocaldb.exe delete v11.0
Run your project
Register a user

edited Jan 23 '15 at 14:52

rchang

5,150
1
15
25

answered Jan 15 '15 at 02:02

Mask

399
3
5

Instead of `v11.0` enter `MSSQLLocalDB` that in web config connection string is like this `data source=(LocalDb)\MSSQLLocalDB;...` – Zoha Jul 28 '19 at 14:13
Don't forget to start server with **sqllocaldb.exe start v11.0** – Shriram Panchal Sep 18 '22 at 03:48

score 16 · Answer 4 · answered Sep 17 '13 at 05:06

16

Remove this line from the connection string that should do it ;) "AttachDbFilename=|DataDirectory|whateverurdatabasenameis-xxxxxxxxxx.mdf"

answered Sep 17 '13 at 05:06

Ahmad Moussa

1,296
18
22

Worked for me too. This should probably be the answer. – Matt West Aug 25 '15 at 20:48
12

Wait . What? Either you want to specify a db-file, then you absolutely need this line, or you don't. Then you absolutely have to ommit this line. This is in no way adressing the underlying problem. – Xan-Kun Clark-Davis Nov 15 '15 at 10:22

score 7 · Answer 5 · answered Nov 18 '15 at 13:54

"Cannot attach the file 'C:\Github\TestService\TestService\App_data\TestService.mdf" as database 'TestService'

When you meet the above error message, Please do the following steps.

Open SQL Server Object Explorer
Click refresh button.
Expand (localdb)\MSSQLLocalDB(SQL Server 12.x.xxxxx - xxxxx\xxxx)
Expand Database
Please remove existed same name database
Click right button and then delete
Go back to your Package Manage Console
Update-Database

score 4 · Answer 6 · answered Nov 17 '14 at 13:27

I have faced the same issue. The following steps in VS 2013 solved the problem for me:

In Server Explorer add new Connect to Database
Select Microsoft SQL Server Database File as Data source
Choose database filename as it should be in according to connection string in your web.config
New database file was created and two database connections was appeared in Server Explorer: "MyDatabaseName" and "MyDatabaseName (MyProjectName)"
Delete one connection (I've deleted "MyDatabaseName")

:-) It sounds to me, that you once deleted the mdf file via explorer and not with the **Database.Delete();** command. — Xan-Kun Clark-Davis, Nov 15 '15 at 10:25

score 3 · Answer 7 · edited Jun 20 '20 at 09:12

To fix this using SQL SERVER Management Studio

Your problem: You get an error such as 'Cannot attach the file 'YourDB.mdf' as database 'YourConnStringNamedContext';

Reason: happens because you deleted the backing files .mdf, ldf without actually deleting the database within the running instance of SqlLocalDb; re-running the code in VS won't help because you cannot re-create a DB with the same name (and that's why renaming works, but leaves the old phantom db name lying around).

The Fix: I am using VS2012, adopt similarly for a different version.

Navigate to below path and enter

c:\program files\microsoft sql server\110\Tools\Binn>sqllocaldb info

Above cmd shows the instance names, including 'v11.0'

If the instance is already running, enter at the prompt

sqllocaldb info v11.0

Note the following info Owner: YourPCName\Username , State: Running , Instance pipe name: np:\.\pipe\LOCALDB#12345678\tsql\query , where 123456789 is some random alphanumeric

If State is not running or stopped, start the instance with

sqllocaldb start v11.0

and extract same info as above.

In the SS Management Studio 'Connect' dialog box enter

server name: np:\.\pipe\LOCALDB#12345678\tsql\query

auth: Windows auth

user name: (same as Owner, it is grayed out for Win. auth.)

Once connected, find the phantom DB which you deleted (e.g. YourDB.mdf should have created a db named YourDB), and really delete it.

Done! Once it's gone, VS EF should have no problem re-creating it.

score 2 · Answer 8 · answered Jul 12 '17 at 14:24

2

You already have an old copy of that database installed in Server Explorer. So its a simple naming collision in the Server Object Explorer / SQL server. You likely created the same database Catalog Name already before you decided to move it to the Apps_Data folder. So that Database name already exists and just needs to be deleted.

Just go into Visual Studio > View > SQL Server Object Explorer and delete the old database name and its connection. Retry your app again and it should install the .mdf file in App_Data and create the same exact database again in the Server Explorer.

answered Jul 12 '17 at 14:24

Stokely

12,444
2
35
23

1

this is the right answer as it actually addresses the core problem. the other answer only provides the workaround. – Nick Fleetwood Dec 27 '17 at 01:18
That's right, this and the answer from @YTKim - all the other answers still leave the old database reference sitting around in the LocalDB config, and won't fix the same problem twice. – Samuel Jaeschke Jan 12 '18 at 13:18

score 2 · Answer 9 · answered Jan 30 '18 at 21:51

Ran into this issue. Caused in my case by deleting the .mdf while iispexress was still running and therefor still using the DB. Right click on iisexpress in system tray and click exit THEN delete the MDF to prevent this error from actually occurring.

To fix this error simply within VS right click the App-Data folder add new item > SQL Server Database. Name: [use the database name provided by the update-database error] Click Add.

thanks, this is the right answer – Zhar Oct 21 '21 at 22:36 — Zhar, Oct 21 '21 at 22:36

score 1 · Answer 10 · answered Apr 30 '14 at 05:21

As per @davide-icardi, remove the "Initial Catalog=xxx;" from web.config, but also check for your azure publish profile file to remove it from here too:
[YourAspNetProject path]\Properties\PublishProfiles[YourAspNetProjectName].pubxml

<PublishDatabaseSettings>
  <Objects xmlns="">
    <ObjectGroup Name="YourAspNetProjectName" Order="1" Enabled="True">
      <Destination Path="Data Source=AzureDataBaseServer;Initial Catalog=azureDatabase_db;User ID=AzureUser_db_sa@AzureDataBaseServer;Password=test" />
      <Object Type="DbCodeFirst">
        <Source Path="DBMigration" DbContext="YourAspNetProjectName.Models.ApplicationDbContext, YourAspNetProjectName" MigrationConfiguration="YourAspNetProjectName.Migrations.Configuration, YourAspNetProjectName" Origin="Configuration" />
      </Object>
    </ObjectGroup>
  </Objects>
</PublishDatabaseSettings>

score 1 · Answer 11 · answered May 17 '17 at 06:37

1

I found that commenting out the context section used to initialise the database resolved the problem. Havnt had time to find out what was wrong with the seeding statements yet, but removing the seeding resolved the problem.

answered May 17 '17 at 06:37

Steven

11
2

Shujat Munawar · Answer 12 · 2018-07-30T21:55:14.010

Just change database name from web.config project level file and then update database.

connectionString = Data Source =(LocalDb)\MSSQLLocalDB;AttachDbFilename="|DataDirectory|\aspnet-Project name-20180413070506.mdf";Initial Catalog="aspnet--20180413070506";Integrated

Change the bold digit to some other number:

connectionString = Data Source==(LocalDb)\MSSQLLocalDB;AttachDbFilename="|DataDirectory|\aspnet-Project name-20180413070507.mdf";Initial Catalog="aspnet--20180413070507";Integrated

score 1 · Answer 13 · edited Feb 23 '20 at 05:40

I didn't read all the responses, but if your .mdf file is NOT part of the SQL installation path, like C:\Temp, then the SQL Database Engine service account(s) will not have permission to create and write to the .ldf file or even read the .mdf file.

The solution is you have to give file system permissions to the SQL Server database engine service account that runs the SQL instance service. So for me, I gave permissions full control via Windows Explorer to my C:\Temp path that contained my .mdf file for the NT Service\MSSQL$SQLEXPRESS01 and NT Service\MSSQL$MSSQL2016 accounts.

Here is a Microsoft article that details this very issue.

score 0 · Answer 14 · answered Jun 09 '13 at 18:42

0

Recreate your database. Do not delete it and your app should continue to work.

answered Jun 09 '13 at 18:42

STLDev

5,950
25
36

4

Sorry for the dumb question but how do I recreate the database? – John Mayer Jun 09 '13 at 18:49
Ooh - this may be worse than I thought. Do you have a copy of the .mdf file anywhere? Where did you get it from originally? Perhaps you have a SQL Server script that will re-create the database for you. – STLDev Jun 09 '13 at 19:12
Sorry for the late reply: I've generated it with the following code: `public class OdeToFoodDB : DbContext { public DbSet Restaurants { get; set; } public DbSet Reviews { get; set; } }` And then I initialize it (but here I get the error): `OdeToFoodDB db = new OdeToFoodDB(); public ActionResult Index() { var model = db.Restaurants.ToList(); return View(model); }` – John Mayer Jun 10 '13 at 14:37
I tried renaming the dbcontext class and now it works. What could have caused the issue? – John Mayer Jun 10 '13 at 17:02

Xan-Kun Clark-Davis · Answer 15 · 2015-11-15T10:57:12.870

I had the same error. Weird thing was, I had one new project form scratch, there it worked perfectly and another, much bigger project, where I always ran into that error message.

The perfecrtly working project (nearly) always creates the database (indluding the files) automatically. It can be any command, read, write, update. The files get created. Of course it uses

DropCreateDatabaseIfModelChanges

There is only one case, when it gets troubled: IF the mdf is auto-created and you delete the mdf and log file. Then that was about it. Say good-bye to your autocreation...

The only way I found to fix it was like mentioned:

sqllocaldb stop v11.0 & sqllocaldb delete v11.0

After that, everything is back to normal (and all other Databases handled by LocalDB also gone!).

EDIT: That was not true. I just tried it and the v11.0 gets automatically recreated and all mdfs stay available. I have not tried with "non file based" LocalDBs.

The confusing thing is, I also got this error if something else was wrong. So my suggestion is, if you want to make sure your DB-Setup is sound and solid: Create a new solution/project from scratch, use the most basic DB commands (Add an entity, show all entities, delete all entities) and see if it works.

If YES, the problem is somewhere in the abyss of VS2013 config and versioning and nuget and stuff.

IF NO, you have a problem with your LocalDB installation.

For anyone who really wants to understand what's going on in EF (and I am still not sure if I do :-) ) http://odetocode.com/Blogs/scott/archive/2012/08/14/a-troubleshooting-guide-for-entity-framework-connections-amp-migrations.aspx

P.S.: Nudge me if you need the running example project.

score 0 · Answer 16 · answered Aug 05 '16 at 20:38

Strangely, for the exact same issue, what helped me was changing the ' to 'v11.0' in the following section of the config.

<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>

score 0 · Answer 17 · answered Mar 14 '17 at 06:27

I recently ran into the same problem. Here is one thing to check. in visual studio there are three place we should check and remove the database.

1. Solution Explorer's App_Data folder
2. Server Explorer's Data Connection menu
3. SQL Server Object Explorer

When I delete database from the first two point, the error still occurs. So, I needed to delete the database from the SQL Server Object Explorer as well. Then I could easily run the 'update-database' command without error. Hope this helps.

David · Answer 18 · 2017-12-21T05:30:48.353

0

I had the same error while following the tutorial on "Getting Started with ASP.NET MVC 5 | Microsoft Docs". I was on Visual Studio 2015. I opened View-> SQL Server Object Explorer and deleted the database named after the tutorial, then it could work. see Delete .mdf file from app_data causes exception cannot attach the file as database

edited Dec 21 '17 at 05:30

answered Dec 21 '17 at 01:42

David

9
1
3

score 0 · Answer 19 · answered Jan 04 '18 at 14:28

We just ran into this ourselves when running dotnet ef database update using ASP.Net Core 2.1; looks like relative paths aren't supported with AttachDbFileName.

System.Data.SqlClient.SqlException (0x80131904): Cannot attach the file '.\OurDbName.mdf' as database 'OurDbName'.

Just wanted to express that here for other people who might be bumping into this. The "fix" is use absolute paths.

See also: https://github.com/aspnet/EntityFrameworkCore/pull/6446

score 0 · Answer 20 · answered Feb 10 '18 at 22:56

If you happen to apply migrations already this may fix it.

Go to your Web.config and update your connection string based on your migration files.

Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-GigHub-201802102244201.mdf;Initial Catalog=aspnet-GigHub-201802102244201;

The datestrings should match the first numbers on your Migrations.

score 0 · Answer 21 · answered Sep 26 '18 at 08:15

0

Ran into the similar problem not exactly the same, A case of Database already existed the issue was solved by following code.

<add name="DefaultConnection" connectionString="Data Source=.;AttachDbFilename=|DataDirectory|\aspnet-EjournalParsing-20180925054839.mdf;Initial Catalog=aspnet-EjournalParsing-20180925054839;Integrated Security=True"
      providerName="System.Data.SqlClient" />

answered Sep 26 '18 at 08:15

Suman Thapa Magar

201
3
7

Delete the same file in datasource and give your location which can be local or server – Suman Thapa Magar Sep 26 '18 at 08:16

Cannot attach the file *.mdf as database

21 Answers21

Linked