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?
1 Like
Run SQL Server Management Studio as an Administrator. (right click-> run as administrator)
Hope, this might work in your case.
Copy the existing files to the "standard" file folder. Or use a different folder if you prefer. The main this is: do not attempt to attach the only copy of the files. Reason: attach can change the contents of the file, and a failed attach could make it (almost) impossible to ever attach the files again. Thus, don't risk it on the original files, only on a copy. If the attach fails, you can copy the files again and re-try.
As to specific mechanics, open SSMS an issue a CREATE DATABASE ... FOR ATTACH command. Don't use the gui.
CREATE DATABASE [db_name] --<<--use any db_name you want
ON PRIMARY ( NAME = db_name_data, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB,
FILENAME = 'c:\Program Files (x86)\...\HEALSExamsSQL.mdf' )
LOG ON ( NAME = db_name_log, MAXSIZE = UNLIMITED, FILEGROWTH = 2MB,
FILENAME = 'c:\...\HEALSExams_log.ldf' )
FOR ATTACHGenerally SQL Database Corrupted due to some important reason which is following -
- Internal error of software and hardware.
- Power failure when software suddenly shutdown
- When System is in shutdown mode and data is coping.
You can get solution from - https://gallery.technet.microsoft.com/Free-SQL-Server-Recovery-91968d9d
Help