28

How can I open a *.mdf file of a database created with Visual Studio 2010, into SQL Server Management Studio?

I tried to attach it by myself, but I cannot navigate to that file for (I guess) some security reason.

bad_coder
  • 11,289
  • 20
  • 44
  • 72
user198003
  • 11,029
  • 28
  • 94
  • 152
  • 1
    Do you have an instance of SQL Server installed on your machine? You can't just "open" an .mdf file with Management Studio, you need to attach it to a database engine, and it usually also requires the .ldf file, however the .mdf file may be corrupt depending on where you got it and what state it was in when it was detached from wherever it came from. Suggest getting a .bak and restoring that instead of dealing with raw .mdf files. – Aaron Bertrand Oct 10 '13 at 13:34
  • mdf is not corrupted. i created it using visualstudio express 2010, and i can work with it. only thing i want is to manipulate with it using sql server management studio. – user198003 Oct 10 '13 at 13:38
  • Attach it to your local instance of SQL Server. This is probably `.\SQLEXPRESS` if you took the defaults with Visual Studio. – Aaron Bertrand Oct 10 '13 at 13:45
  • I'm a VS2017 User. My instance is -- (localdb)\MSSqlLocalDb – George Beier Feb 09 '21 at 18:09

5 Answers5

17

I had the same problem.

system configuration:-single system with window 7 sp1 server and client both are installed on same system

I was trying to access the window desktop. As some the answer say that your Sqlserver service don't have full access to the directory. This is totally right.

I solved this problem by doing a few simple steps

  1. Go to All Programs->microsoft sql server 2008 -> configuration tools and then select sql server configuration manager.
  2. Select the service and go to properties. In the build in Account dialog box select local system and then select ok button.

enter image description here

Steps 3 and 4 in image are demo with accessing the folder

djv
  • 15,168
  • 7
  • 48
  • 72
rakeshyadvanshi
  • 294
  • 3
  • 9
10

I found this detailed post about how to open (attach) the MDF file in SQL Server Management Studio: http://learningsqlserver.wordpress.com/2011/02/13/how-can-i-open-mdf-and-ldf-files-in-sql-server-attach-tutorial-troublshooting/

I also have the issue of not being able to navigate to the file. The reason is most likely this:

The reason it won't "open" the folder is because the service account running the SQL Server Engine service does not have read permission on the folder in question. Assign the windows user group for that SQL Server instance the rights to read and list contents at the WINDOWS level. Then you should see the files that you want to attach inside of the folder.

(source: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c80d8e6a-4665-4be8-b9f5-37eaaa677226/cannot-navigate-to-some-folders-when-attempting-to-attach-mdf-files-to-database-in-management?forum=sqlkjmanageability)

One solution to this problem is described here: http://technet.microsoft.com/en-us/library/jj219062.aspx I haven't tried this myself yet. Once I do, I'll update the answer.

Hope this helps.

Juan Paredes
  • 769
  • 4
  • 12
  • 22
  • (For the Database Engine per service SID, use NT SERVICE\MSSQLSERVER for a default instance, or NT SERVICE\MSSQL$InstanceName for a named instance.) – Stephen Oberauer Nov 09 '14 at 17:47
7

Copy the files to the default directory for your other database files. To find out what that is, you can use the sp_helpfile procedure in SSMS. On my machine it is: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA. By copying the files to this directory, they automatically get permissions applied that will allow the attach to succeed.

Here is a very good explanation :

How to open MDF files .

JAN
  • 21,236
  • 66
  • 181
  • 318
  • Note that when the file is generated and created on breakpoint, it will be inaccessible due to another process using it. – FanaticD Mar 29 '16 at 06:49
4

I don't know about the older versions but for SSMS 2016 you can go to the Object Explorer and right click on the Databases entry. Then select Attach... in the context menu. Here you can browse to the .mdf file and open it. screenshot

wecky
  • 754
  • 9
  • 17
1

I don't know if this answer can be found on the links above, but I just run SQL Management Studio as Administrator and it worked.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Catalin
  • 11
  • 1