Last post May 25, 2017 06:40 PM by egillporkelsson
May 24, 2017 01:23 PM|egillporkelsson|LINK
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
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?
May 24, 2017 06:00 PM|PatriceSc|LINK
With "Jet" the db engine is loaded by your application (ie you web site) and then works directly on the database files.
With SQL Server you just connect to a db engine (that can run on another server) which in turn works with the database files. You can perfectly create a web application without ever dealing with database files and without even having access to those files
(this is done under the account used by the database service).
For for now it seems to me :
- you deleted the mdf file attached to the db engine so the db engine knows this database "exists" but then can't find any more this file
- then you try to attach another database file but I suspect that the database engine runs under an account that doesn't have access to this folder
For now I would suggest to try to go back at the initial situation. KEEP A COPY of those files so that you can try other things if it fails.
I would try :
- stop SQL Server
- put this fle back where it was (c:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\)
- start SQL Server
SSMS is just a client side tool that connect to a database engine. Attach/detach is not about SSMS connecting to a file. It allows to tell to the db engine which database files should be used.
May 25, 2017 02:40 PM|matthewofarrell|LINK
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:
In regards to your problem: the files have been moved. do you know where those files are now?
May 25, 2017 06:40 PM|egillporkelsson|LINK
Hi, Thank you all for your time and great help. I really appreciate your feedback and I am very thankful for your time and support.