Cannot create file '*.mdf' because it already exists

Question

I am using Entity-Framework in a Winforms application. The application connects to MSSQLServer and everything works fine. Then I detach the database and copy the .mdf file to the app folder and change the connection string to use the local .mdf file, still everything works fine.

The problem is that when I copy my application together with the database to another PC suddenly EF tries to recreate the database and throws this error:

Cannot create file 'Path\MyDatabaseName.mdf' because it already exists. Change the file path or the file name, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Context Initializer is set to MigrateDatabaseToLatestVersion:

Database.SetInitializer<MyContext>(new MigrateDatabaseToLatestVersion<MyContext, DAL.Migrations.Configuration>("MyConnectionString"));

Also, automatic migration is off and database is created by EF Code-First on Sql-server and is already updated to the latest migration so no migration is needed.

Connection string for the first scenario is:

<add name="MyConnectionString" connectionString="Server=DESKTOP-XXXXXXX; Database=MyDatabase; Integrated Security=True; Connect Timeout=30;" providerName="System.Data.SqlClient">

and for local .mdf file is:

<add name="MyConnectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=|DataDirectory|\MyDatabase.mdf; Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />

So, What is going on here??

Nagey

What are the connection strings for the 3 scenarios? – Downright

@Downright I updated the question – Nagey

Do both machines have the same version of SQL server? (SQLExpress or whatever). It would be a good idea to specifiy the full path to the MDF, rather than |DataDirectory|. – Downright

The development machine has SQLServer 2016 but on the target machine I installed SQLLocalDB – Nagey

@Downright Do you think that this is happening because versions are different? – Nagey

I think a path problem is more likely. Have you tried specifying a full path in the connection string? – Downright

@Downright No I haven't tried full path. But if I delete the database, EF creates an empty database in the |DataDirectory| folder. So we can be sure that the path is correct. Basically this is the confusing part: EF ignores the existence of the database file and tries to overwrite it with a new file. – Nagey

Answer 1

In my case, go to C:\Program Files\Microsoft SQL Server\SQLSERVER\MSSQL\DATA folder and delete the same database name '*.mdf' file if exist. After deleting it and recreate the database, the problem is resolved.

Compensate

Answer 2

For SQL Server running in docker:

Since I am using an SQL server in docker I have to get inside the docker using docker exec -it <CONTAINER ID> bash and delete mdf file from the/var/opt/mssql/data directory.

Steps:

1. docker ps -> Copy CONTAINER ID of the docker container running SQL Server
2. docker exec -it <CONTAINER ID> bash -> Now you can view/modify a file inside the docker
3. cd /
4. cd /var/opt/mssql/data -> To go to the directory of mdf/ldf file
5. rm -rf <database_name>.mdf -> To delete mdf file
6. rm -rf <database_name>_log.ldf -> To delete ldf file

This resolves my issue.

Servitor

Answer 3

The solution to this problem is to do the following:

1. Open up your command prompt

2. Open services (type in services.msc)

3. Find your SQL Server Service instance -> right click -> then click STOP

4. Navigate to the path specified (ex.Path\MyDatabaseName.mdf) and delete it or create a backup if you need to access it later (You will likely have to do the same with the log file as well)

5. Retry your attempted operation

Kill