none
.mdf and .ldf Files open in SQL server?

    Question

  • I have two files which are extension .mdf and .ldf i am doing project of my university. so i want to eopn these files i m recently installed SQL server express editions i am already tried to open it but failed. Any body tell me how can i open it?
    Tuesday, February 08, 2011 4:05 PM

Answers

  • If you are new to SQL Server, try this:

    1. On the "Start Menu", click "All Programs", "Microsoft SQL Server 2008 R2", and then click "SQL Server Management Studio".
    2. On the "Connect to Server" dialog, type .\SQLEXPRESS and then click the Connect button.
    3. Expand the "Databases" node in the "Object Explorer" window on the left side.
    4. Right-click on "Databases" node, and then click "Attach...".
    5. Click the "Add" button on the "Attach Databases" dialog.
    6. Navigate to the folder that contains your .MDF file.
    7. Select it.
    8. Click OK.
    9. Click OK again.
    10. The database should be attached to SQL Server now.
    11. Look in the "Databases" node. If you don't see the database, press F5.
    12. Now your database is there, you can expand it. All of the data would be in the tables.
    13. Expand the "Tables" node of your database.
    14. Right-click on any table, and then "Select Top 1000 Rows..." This is an easy way to see the data.

    I hope this helps. As stated above, you can not simply open the MDF file. You have to attach it and then you can use a tool like SQL Server Management Studio to view the data. Here is a new video that I created to show how to attach databases: How to open an .MDF file? (Attach a Database in SQL Server). If you get any errors, I tried to explain possible causes in this blog post: Attach Tutorial and Troubleshooting.

    To summarize here, if you get errors trying to attach, make sure that you have proper permissions. Don't put the MDF and LDF files in a directory that requires admin permissions like C:\. It's better to put them into the DATA directory of SQL Server. This could be (for 2008 R2): C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA. And make sure that the files are not readonly. If all that fails, change permissions on the files to give your logged-on user Full Control over them.

    Jason Roth

    Wednesday, February 09, 2011 12:15 AM

All replies

  • CREATE DATABASE ... FOR ATTACH (documented in Books Online). You can get to this also from Management Studio, right-click Databases Folder and select "Attach Database...".
    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, February 08, 2011 4:36 PM
  • Shahid,

    I am confused with your question how are you trying to open this files ? .MDF and .LDF are data and log files .

    Can you post the error message you are getting ?

    -------------------------
    Thanks,Suhas V
    Tuesday, February 08, 2011 4:41 PM
  • To put this another way, the SQL Server Database Engine opens the database files. You can't open them by yourself. If the database is installed on SQL Server, the data file (mdf) and log file (ldf) will be opened when you start the SQL Server service (using the SQL Server Configuration Manager). The Database Engine will take an exclusive lock and prevent any other process from looking at the files. If the database has been detached from the Database Engine, start the Database Engine and use the ALTER LOGIN FOR ATTACH statement to attach the database, so that the Database Engine can open it. Or use SQL Server Enterprise Manager tool which has an attach option.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, February 08, 2011 4:49 PM
    Answerer
  • If you're new to SQL Server, it will be a lot easier to use a UI to attach your DB. I poked around YouTube and found this video that shows you the steps. Start the video at 3:45. http://www.youtube.com/watch?v=xdI-DP_5vyc

    Hope this helps.

    Jason Roth

    Tuesday, February 08, 2011 8:04 PM
  • Dear Suhas,

    I am trying to open data files. Actually I am doing my uni project so my friend send me these files and said open to SQL Server so i installed SQL server Express so i m new simply i try my best to open but failed. if u know so plz tell me in detals.

    Cheers.

    shahid

    Tuesday, February 08, 2011 11:47 PM
  • If you are new to SQL Server, try this:

    1. On the "Start Menu", click "All Programs", "Microsoft SQL Server 2008 R2", and then click "SQL Server Management Studio".
    2. On the "Connect to Server" dialog, type .\SQLEXPRESS and then click the Connect button.
    3. Expand the "Databases" node in the "Object Explorer" window on the left side.
    4. Right-click on "Databases" node, and then click "Attach...".
    5. Click the "Add" button on the "Attach Databases" dialog.
    6. Navigate to the folder that contains your .MDF file.
    7. Select it.
    8. Click OK.
    9. Click OK again.
    10. The database should be attached to SQL Server now.
    11. Look in the "Databases" node. If you don't see the database, press F5.
    12. Now your database is there, you can expand it. All of the data would be in the tables.
    13. Expand the "Tables" node of your database.
    14. Right-click on any table, and then "Select Top 1000 Rows..." This is an easy way to see the data.

    I hope this helps. As stated above, you can not simply open the MDF file. You have to attach it and then you can use a tool like SQL Server Management Studio to view the data. Here is a new video that I created to show how to attach databases: How to open an .MDF file? (Attach a Database in SQL Server). If you get any errors, I tried to explain possible causes in this blog post: Attach Tutorial and Troubleshooting.

    To summarize here, if you get errors trying to attach, make sure that you have proper permissions. Don't put the MDF and LDF files in a directory that requires admin permissions like C:\. It's better to put them into the DATA directory of SQL Server. This could be (for 2008 R2): C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA. And make sure that the files are not readonly. If all that fails, change permissions on the files to give your logged-on user Full Control over them.

    Jason Roth

    Wednesday, February 09, 2011 12:15 AM
  • And if you don't have SQL Server Management Studio, a free version is available at https://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displayLang=en
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, February 09, 2011 4:02 PM
    Answerer