Core 6 .mdf not opening in MSSMS

Question

Core 6 .mdf not opening in MSSMS

Dean Everhart 1,541

Problem Description

brand new Core 6 project with simple database with two records (property sets) added to it using SQLExpress.

MSSMS unable to open .mdf file throwing errors (error log below).

Steps Taken So Far:

Verified full access to .mdf file to user accounts on computer.

Error:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE FILE encountered operating system error 2(The system cannot find the file specified.) while attempting to open or create the physical file 'C:\Users\User1\WebApplicationProjectLevel.Data'. (Microsoft SQL Server, Error: 5123)

For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5123-database-engine-error

------------------------------

BUTTONS:

OK

------------------------------

SurferOnWww 4,721 Reputation points

2022-11-14T00:39:02.983+00:00

Dis you attach the .mdf file to the SQL Server?

Accepted answer

2 additional answers

Your answer

SurferOnWww 4,721 Reputation points

2022-11-14T00:39:02.983+00:00

Dis you attach the .mdf file to the SQL Server?

Answer 1

Bruce (SqlWork.com) 78,086 Volunteer Moderator

you connect to server: (localdb)\mssqllocaldb

then you will need to attach the database if it is not attached.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver16

note: you will need the full file path to the .mdf

Dean Everhart 1,541 Reputation points

2022-11-19T14:20:06.667+00:00

I'm having a hard time attaching the database through MSSMS through right-clicking on databases folder, selecting attach, clicking Add, and navigating, through MSSMS to the .mdf file.

At this point the .mdf file doesn't appear when filtering for .mdf files. When I change the filter to all file types, it also does not appear.

Double checking the properties on the .mdf file, it reads .mdf.

The only thing that I see that is a red flag to me is that the default file name, specified in the connection string includes the path in the name:

WebApplicationProjectLevel.Data.mdf

Could the default connection string including the .Data in the name be causing this problem?
Dean Everhart 1,541 Reputation points

2022-11-19T14:24:04.727+00:00

I created another project with the connection string altered so it did not include the .Data in the name of the .mdf and it is also not seen when filtering .mdf files or all file types.

It is as if these files do not exist to MSSMS, even though the extension clearly reads mdf and properties the same.
Bruce (SqlWork.com) 78,086 Reputation points Volunteer Moderator

2022-11-19T16:57:47.51+00:00

When MSSMS asks Sqlserver to attach a database, the directory is done by Sqlserver. It looks for files in its own Data folders, but you can use the file dialog to locate the mdf.

Answer 2

Bruce (SqlWork.com) 78,086 Volunteer Moderator

Use the same connection string with studio as in the .net 6 project. It should be able to connect to sql express the same way.

Note: SSMS can not open a mdf directly, it needs a sql service instance to attach and expose it. The connect string gives the instance the info to attach.

Dean Everhart 1,541 Reputation points

2022-11-13T19:47:25.723+00:00

Thank you for the answer.

Does "It should be able to connect to sql express the same way" above mean that the existing connections string that allows the project to connect to the database should also work for MSSMS...that I don't need to add anything (i.e. a connection string specifically for MSSMS)?

currently my connection string reads...

"ConnectionStrings": {
"DbContextClass": "Server=(localdb)\mssqllocaldb;Database=WebApplicationProjectLevel.Data;Trusted_Connection=True;MultipleActiveResultSets=true"

or

is the connections string somehow entered into MSSMS directly in the attachment process?

Thanks again.

Dean
Bruce (SqlWork.com) 78,086 Reputation points Volunteer Moderator

2022-11-21T16:40:57.11+00:00

sql management studio must be on the same machine. the server name s/b:

(localdb)\mssqllocaldb

Answer 3

Dean Everhart 1,541

Problem resolved.

Share via

Core 6 .mdf not opening in MSSMS

2 additional answers

Your answer