Solved Unable to open SQL Server database file (.mdf)

Microsoft Sql server 2005 standard editi...
May 24, 2015 at 11:16:32
Specs: Windows 64

I have a SQL Server 2005 database file (a .mdf file) that I am trying to open in SQL Server Management Studio so I can add a field to a table, but I am unable to open the file. I can't say that I really understand how SQL Server handles these databases. I especially don't understand the "attaching" and "detaching" operations.

I am working in VB.Net 2008 now, but my background is with VB6 using the JET engine. It was just so straightforward using the JET engine, but seems so much more complicated with SQL Server. I am trying to use .mdf files the same way I used .mdb files. I want my application to find the database file, "attach to it", and let me manipulate it.

At one time I was able to open my database in Sql Server Management Studio and edit the design of the database. The application still works but I am no longer able to modify the database because I cannot attach to it in Management Studio.

Here is a big clue: the files are no longer in the special folder anymore, which is c:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\. I might have deleted the files, stupidly, thinking they were redundant. I still have the .mdf file (and the _log.ldf file) on my hard drive and the application can still open it, but when I try to use Management Studio to attach to the .mdf file, I get this error:

Microsoft SQL Server Management Studio Express
An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

here are the details:
Unable to open the physical file "c:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\HEALSExamsSQL.mdf". Operating system error 2: "2(The system cannot find the file specified.)". (.Net SqlClient Data Provider)

Indeed the file is not there, but why is Mgmt Studio looking there? Why doesn't it just open the file where it is? What can I do to get it back? I even copied the files to c:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\? I get the same error.

Here is another clue: In Management Studio, when I right-click Databases, and chose Attach, it brings up a dialog. There I click "Add" to get another dialog in which I will select a database, but before the dialog appears I get this error:

Locate Database Files - KURANT-WIN7\SQLEXPRESS
C:\Users\Jason Kurant\Desktop
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

This suggests to me that there is some access control issue, but I can't figure out what it is. Can anyone suggest a way to fix this?

See More: Unable to open SQL Server database file (.mdf)

Report •

May 26, 2015 at 11:52:54
✔ Best Answer
One of the commands and types of restoring must assist you, good luck...

Restore full backup WITH RECOVERY

Note: As mentioned above this option is the default, but you can specify as follows.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'

Recover a database that is in the "restoring" state

Note: The following command will take a database that is in the "restoring" state and make it available for end users.


Restore multiple backups using WITH RECOVERY for last backup

Note: The first restore uses the NORECOVERY option so additional restores can be done. The second command restores the transaction log and then brings the database online for end user use.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'

More information you can dig out from resources directly connected with SQL Server databases and database corruption in MS SQL Server any version...

Report •

March 14, 2016 at 04:54:12
This article describes the following about this hotfix release:

The issues that are fixed by this hotfix package

The prerequisites for installing the hotfix package

Information about whether you must restart the computer after you install the hotfix package

Information about whether the hotfix package is replaced by any other hotfix package

Information about whether you must make any registry changes

The files that are contained in the hotfix package

For more you can also visit:

Report •

Related Solutions

Ask Question