[sql-server] Attach (open) mdf file database with SQL Server Management Studio

Can you help me how can I open *.MDF file of database created with VisualStudio 2010, into SQL Server Management Studio?

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

This question is related to sql-server visual-studio-2010 mdf

The answer is


i don't know if this answer can be found on the links above, but i just run SQL management studio as Administrator and worked. Hope it helps

Cheers


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


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.


You may need to repair your mdf file first using some tools. There are lot of tool available in the market. There is tool called SQL Database Recovery Tool Repairs which is very useful to repair the mdf files.

The issue might me because of corrupted transaction logs, you may use tool SQL Database Recovery Tool Repairs to repair your corrupted mdf file.


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


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 .


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to visual-studio-2010

variable is not declared it may be inaccessible due to its protection level SSIS Excel Connection Manager failed to Connect to the Source This project references NuGet package(s) that are missing on this computer Gridview get Checkbox.Checked value error LNK2038: mismatch detected for '_MSC_VER': value '1600' doesn't match value '1700' in CppFile1.obj What is the difference between Visual Studio Express 2013 for Windows and Visual Studio Express 2013 for Windows Desktop? Attach (open) mdf file database with SQL Server Management Studio What is and how to fix System.TypeInitializationException error? Could not load file or assembly "Oracle.DataAccess" or one of its dependencies IIS error, Unable to start debugging on the webserver

Examples related to mdf

Attach (open) mdf file database with SQL Server Management Studio SQL Server: Importing database from .mdf? What is an MDF file?