CODEWITHSUNDEEP
.netasp.net-mvcdatabaseentity-frameworkasp.net-mvc-4
John Mayer
John Mayer

Reputation: 3363

Cannot attach the file *.mdf as database

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.

Upvotes: 81

Views: 125768

Answers (21)

Big H
Big H

Reputation: 31

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.

Upvotes: 1

Suman Thapa Magar
Suman Thapa Magar

Reputation: 267

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

Upvotes: 0

Shujat Munawar
Shujat Munawar

Reputation: 1677

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

Upvotes: 1

Crismogram
Crismogram

Reputation: 936

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.

Upvotes: 0

Rick Drayson
Rick Drayson

Reputation: 29

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.

Upvotes: 2

John Reilly
John Reilly

Reputation: 6309

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

Upvotes: 0

David
David

Reputation: 19

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

Upvotes: 1

Stokely
Stokely

Reputation: 15947

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.

Upvotes: 2

Steven
Steven

Reputation: 11

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.

Upvotes: 1

Foyzul Karim
Foyzul Karim

Reputation: 4502

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.

Upvotes: 0

Vaibhav
Vaibhav

Reputation: 2557

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>

Upvotes: 0

Chris
Chris

Reputation: 8647

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.

Upvotes: 61

joedotnot
joedotnot

Reputation: 5153

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.

Upvotes: 3

YTKim
YTKim

Reputation: 71

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

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

Upvotes: 7

Xan-Kun Clark-Davis
Xan-Kun Clark-Davis

Reputation: 2843

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.

Upvotes: 0

Mask
Mask

Reputation: 399

  1. From Package Manager Console run:

    sqllocaldb.exe stop v11.0

    sqllocaldb.exe delete v11.0

  2. Run your project

  3. Register a user

Upvotes: 37

CI Apps
CI Apps

Reputation: 289

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

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

Upvotes: 4

OzBob
OzBob

Reputation: 4530

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>

Upvotes: 1

Davide Icardi
Davide Icardi

Reputation: 12229

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

Upvotes: 131

Ahmad Moussa
Ahmad Moussa

Reputation: 1324

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

Upvotes: 16

STLDev
STLDev

Reputation: 6174

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

Upvotes: 0

Related Questions