Unable to open SQL Server database file (.mdf)

orlandodiaz

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?

postsaurav

Hi,
I think you kept the mdf file in desktop. If that is the case then -
1) Create a folder in any of the drive, Place the file there and then try to attach the file on sql.
2) Run SQL Server as administrator.

matt_quelch

Hi Orlando,

It sounds like you are trying to restore a multi-file database with only data file which may not work. Try the following script which will let you just attach the single file you have. This will re-create the log file, however if it really is a multi-file database it won't work:

EXEC sp_attach_single_file_db @dbname='MyDatabase', @physname='C:\Path To\Your Data\File\TestDb.mdf'

I'm guessing this isn't a NAV database, although it doesn't overly matter - just if it is NAV you shouldn't be adding fields to tables through SQL

SQL can be quite a complex beast so it may be worth looking into a SQL course.

matt_quelch

Having had another read through your thread, another point to keep in mind is that when you are working with files in SSMS, the files you use and reference are being accessed on the server and by the user account that runs the SQL Server service.

So even though you can see and access the file yourself in Windows, that doesn't always mean SQL can see and indeed read it as well! Just one to bear in mind

hamadzaher

The repair method attempts to recover only the tables, indexes and queries in the database. Do not attempt to repair damaged forms, reports, macros and modules. Before executing the Compact and Repair tool, please ensure the following condition:

1. Do not open Access database must closed
2. Sufficient storage space available - minimum double in size of your Access database on that Disk.
3. Close the .mdb file related to .ldb file before you delete the .ldb file.
4. Then Run the Compact and Repair tool

Recommended actions for corrupt or suspect databases is a guide below and informative threads with bonus solution, which created for very complicated cases with .mdf files
https://www.openfiletool.com/mdfopen.html MDF Open File Tool

samuelwellsa

There are multiple manual techniques available, which helps you to repair your corrupted MS SQL database files. Some manual solutions are listed below such as

* Using DBCC CHECKDB
* Using MS SQL Server Management Studio

Check here the step by step solution to repair corrupted MS SQL database files. Visit: http://www.filesrecoverytool.com/sql-database-repair.html

jeremyscastongs

Advanced algorithm that can be used to repair corrupt database of SQL MDF file. This tool is also capable to recover deleted tables and records of SQL tables with original format. Go through- http://www.recoverydeletedfiles.com/sql-database-recovery-software.html

shinyvictor

When executing this, getting error Device not ready

EXEC sp_attach_single_file_db @dbname=’TestDb’,
@physname=N’D:\Temp.mdf’
GO

Msg 5133, Level 16, State 1, Line 1 Cloud Computing Training in Chennai
Directory lookup for the file “D:\Temp.mdf” failed with the operating system error 21(The device is not ready.).

How to resolve? Dot net Training in Chennai

Linux Training in Chennai | Salesforce training in Chennai

Sieneskil

There's not really a gentle way to say this: Stop doing that. The GUI is full of bugs and bad defaults; more importantly, unless you take a screencast, we have no way to identify exactly what steps you're taking as you point and click your way through the GUI.

RobinShanab

There's not really a gentle way to say this: Stop doing that. The GUI is full of bugs and bad defaults; more importantly, unless you take a screencast, we have no way to identify exactly what steps you're taking as you point and click your way through the GUI.

Log in as a system administrator (preferably the sa account). If you're not a system administrator, or have lost the sa password, see this tip to get it back.

Make sure both files are in this location (you should never be pointing SQL Server at user folders like ...\Jason Kurant\...):

Well, they are not the same. This is similar to saying you want to use your tractor in a Nascar race, but drive it the same way you drive it on the farm, and expect to win. If you want to use SQL Server, you need to sit down and learn how SQL Server works, and forget about Access.

There is one more reason which is corruption in MDF file. To repair the corrupt MDF file, you can use SQL database repair software.