Resolving a Common SQL Database Creation Error in .NET Core

Welcome to today’s blog.

In today’s post, I will be showing how to resolve a common error that I had recently when running a .NET Core application under the development environment.

The common error relates to a development task where I was running a .NET Core application that was connecting to a database.  

A Common Database Creation Error?

The error is the SQL database creation error shown below:

As you can see, the error looks likely to be caused by us attempting to re-create the database when it is already existing within the database data folder.

The raw error is as shown:

.NET Core 4.6.26614.01 X64 v4.0.0.0    |   Microsoft.AspNetCore.Hosting version 2.0.3-rtm-10026    |    Microsoft Windows 10.0.18362    |   Need help?System.Data.SqlClient.SqlException (0x80131904): Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\aspnet-BookLoan.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. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at

The error and its content suggest to us that we have referenced the wrong file, however when we connect to a SQL database within an application, we would use a connection string, and a connection string does not use a direct file path, it uses a server name, a data context, and either credentials or through an existing trusted connection.

The .NET framework SQL Exception error is as shown:

Internal Server Error
An error occurred while starting the application.
SqlException: Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\aspnet-BookLoan.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.

The error message generated within the application source does not seem to give us any further details in determining the real root cause of the problem. What else can we check?

Looking at Possible Causes of the Error

To attempt to resolve the issue I tried the following:

1. Check the connection string.
2. Check the database exists within the SSMS databases.

If the database is not showing under the databases within SSMS then you will need to attach the database into your SQL Server host server.

Do this as follows:

Select Databases under your host server e.g. localhost. Select Attach…

Select the Database mdf file within your Data folder within the SQL Server installation subfolder:

Select Ok to attach.

Once done you will see the database in SSMS.

Expand and check it is accessible.

Now run the .NET Core application and the database will be accessible and the creation error will disappear.

The reason why the error has disappeared, and the database connection has been successful is because we have managed to re-attach an existing physical database file onto the list of databases that are within the SQL server instance. Without the re-attachment, the connection string that we used to connect to the database did not know of the existence of the database name, its physical folder, and file name within the list of databases.

This type of issue is very common when working with SQL Express databases that are in SQL Server compatible MDB format, that are created locally and stored within a folder within your user profile. After we decide to move from an express database to a local instance of the same database, the action of moving the express database into a SQL server data folder does not automatically make the database visible to the SQL server instance. Resolving this issue required us to stop the SQL instance, re-attach the database to the SQL instance, re-start the instance, then re-connect to the SQL database within the running SQL instance.    

That’s all for today’s post.

I hope this post has been useful and informative.