3 replies
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?
#open #sql #unable
  • Profile picture of the author BjornPorgilsson
    Guys, can help me? I would be grateful for any advice.
    {{ DiscussionBoard.errors[11102384].message }}
  • Profile picture of the author morrishall
    If you don't know how to open mdf file you can some tool for this.
    Or try the next option:
    in short: attaching/detaching is nothing else that to register the database files into a sql instance process, under a database name.
    as from there, you connect to the server just as with jet, and specify a database (catalog) name instead of the database file (access => mdb)
    so, in sql server, you don't "connect" to the .mdf, but to the server process, and ask for a database name. the process will "connect" you to the database files.
    To understand what detach/attach does, you might want to read up here:
    http://www.databasedesign-resource.c...-database.html
    In regards to your problem: the files have been moved. do you know where those files are now?
    {{ DiscussionBoard.errors[11102572].message }}
  • Profile picture of the author BjornPorgilsson
    Thank you buddy for your help and time. your recommendations are very useful.
    {{ DiscussionBoard.errors[11102687].message }}

Trending Topics