MS SQL Server Manager - some databases are not attachable

1

I am a new user of SQLExpress 2008 and need some advise/help.
I have some databases listed in the object explorer, however some are expandable [+] but some are not.
Why? I am using windows authentication. I used the link below trying to solve the problem, but no success.
Trying to attach a database in a solution map/App_Data (not shown in object explorer), I get an error: 5123 and error text:
TITLE: Microsoft SQL Server Management Studio

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

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

ADDITIONAL INFORMATION:

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

CREATE FILE encountered operating system error 32(Det går inte att komma åt filen eftersom den används av en annan process.) while attempting to open or create the physical file 'C:\Inetpub\wwwroot\GridInsert\App_Data\HusFlyttTest.mdf'. (Microsoft SQL Server, Error: 5123)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.6241&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

BUTTONS:
OK

Grateful for any help -

2

is the following path on your machine or on a server?

C:\Inetpub\wwwroot\GridInsert\App_Data

3

Please have a look on here: https://www.slideshare.net/jason_clark03/microsoft-sql-server-error-5123-attach-database-how-to-fix

It might be helpful for you.

4

Dear yosiasz, Thank you for your intention to help me!
The path is on my machine., and I have struggled for days with the probleme. However, I think I have found the reason for this error. I have copied and pasted the database i Win explorer to the path mentioned above, and something went probably wrong with the correponding log file when I renamed the database in explorer and not in Visual Studio/SQL Server Management Studio.
So with no backup yet - (only 25 rows and 4 simple columns) I deleted the old database and recreated a new one, this time placed in ...MSSQL10.MSSQLEXPRESS\MSSQL\DATA folder. After removing "user instance = true" in the connection string in my web config file, also my application runs fine.
Why was it impossible to log in with user instance true? When shall I use "user instance"?
Hopefully you can give me a hint.
Thank you and have a nice day (morning now in Sweden).

5

Dear jason_clark!
Thank you for the link, I learn new things all the time and the content indeed improved my knowledge.
My probleme probably eminated from a corrupted log file - error when renaming after copy and paste.
I'm glad for your helpfulness and wish you a nice day. Thanks!

1 Like
6

what kind of web server are you using? IIS? does the application use an application pool? if so which identity does it use? are you in a windows environment with active directory?

7

You can fix this blunder by using a stored procedure:

sp_attach_single_file_db [@db_name=] ‘db_name’, [@physname=] ‘physical_name

[@db_name =] ‘db_name’ is the database name. ‘db_name’ is nvarchar (128).
[@physname =] ‘phsyical_name’ is the database file name. ‘phsyical_name’ is nvarchar (260).

Example:

EXEC sp_attach_db @name = ‘employer’
@filename1 = ‘C:\MSSQL\Data\employer.mdf
@filename2 = ‘C:\MSSQL\Data\employer_log.ldf

This procedure can help you to remove SQL error 5123.