SQLTeam.com | Weblogs | Forums

Unable to open SQL Server database file (.mdf)


#1

Experts,

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?


#2

You can't really do that. SQL Server stores databases in files with filetypes mdf, ndf and ldf. However only SQL Server can properly use those files.

In SSMS, you can attach a database, by right-clicking the Databases node in Object Explorer and selecting Attach. Navigate to your mdf file and select it. Then click OK. If everything is in order, you will now have a new database that you can see under the Databases node (you may have to right-click and select Refresh). You can then use that database from your application.

Also, note that SQL Server runs under a different account (not your usual login, that is), The SQL Server account needs permissions to attach an mdf file from a folder other than the default folder. (of course it needs permission there as well, but that is taken care of by setup)


#3

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.

Command:
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH RECOVERY
GO

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.

Command:
RESTORE DATABASE AdventureWorks WITH RECOVERY
GO

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.

Command:
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO

More information you can dig out from resources directly connected with SQL Server databases and database corruption in MS SQL Server any version...
http://www.sqlservercentral.com/Forums/Topic1602448-266-1.aspx
http://www.mdf.openfiletool.com/ MDF Open File Tool


#4

If the application is still able to "open" it, then it must already be attached. What do you see in the Databases list when you connect to the server in SSMS?


#6

Try using FOR ATTACH_REBUILD_LOG. may be possible if the the .mdf is corrupted or try to identify last good backup and restore it.