May 6, 2015 at 7:13 am
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?
May 6, 2015 at 9:39 am
You can't "Open" an MDF file. You absolutely need to do the ATTACH process. But, instead of fighting with the GUI, let's go to the file.
I'm assuming that the original database only consisted of a single .MDF file. If you had more than one file storing data, we have to have all of them. But for one:
USE master
GO
CREATE DATABASE HEALSExamsSQL ON
( FILENAME = N'C:\WHEREEVER\YOUWANTIT\ONTHE\DRIVE\HEALSExamsSQL.mdf' )
FOR ATTACH
GO
That will attempt to attach the database and it will recreate the log file (I'm unclear if you have that or not. If you do, follow the T-SQL example here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2015 at 9:41 am
And rereading, You just won't be able to treat the MDF file the way you treated Access files through the Jet engine. It's utterly different. SQL Server is a service that has to be running. The service manages the MDF file. You connect to the service, not attach the file. Different development paradigm.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 29, 2015 at 6:32 am
Corruption in SQL server database can happen due to any uncertain like virus attack, power failure, hardware issue, OS malfunction, and sudden system shutdown, so on. When a SQL server data file (.mdf) is corrupt then DBAs can try several methods to repair and recover data from it.
• Run DBCC CHECKDB: It checks & reports all the error message in error log, if there is any problem with the database. Try to analyze & understand the error message logged in the error log. Re-run DBCC CHECKDB with the recommended minimum repair option to repair the file.
You may read and learn some helpful sources...
http://answers.unity3d.com/questions/738158/how-to-repair-backup-of-mdf-file-database.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply