How to open an .MDF file? (Attach a Database in SQL Server)
Articles,  Blog

How to open an .MDF file? (Attach a Database in SQL Server)


Hi. Let’s say somebody wants to share their
SQL Server database with you. If they give you an .MDF and .LDF file, how
do you actually look at the contents of that database?
You might be tempted to try to open these files with a specific SQL Server program,
like SQL Server Management Studio. But the correct approach is to “attach” these
files to a specific instance of SQL Server. Let me show you how this works.
First, you need a running instance of SQL Server on your machine.
SQL Server 2008 R2 Express is a free edition of SQL Server.
Once installed, you can open up SQL Server Management Studio, and connect to the .SQLEXPRESS
instance. To attach the database, right-click on Databases,
and select Attach. In the attach databases dialog, click Add,
and navigate to the location of your database. Select the database, and click OK.
Note that you only have to select the .MDF file.
The .LDF file is automatically included. Then click OK.
If it works, great! If you get an error like this, click OK, and
then click the hyperlink for the error message. The error we’re getting here is “Access is
denied”, Microsoft SQL Server, Error: 5120. There’s a couple different ways of fixing
this problem, including giving yourself full control over the file.
But one of the easiest solutions is to close SQL Server Management Studio, and reopen it
as an administrator. Right-click SQL Server Management Studio,
and select “Run as Administrator”. When you perform the steps this time, everything
works. Now you’re free to take a look at the data.
There’s one more tip I’d like to share. If I wanted to detach this database, I could
right-click on it, select Tasks, and then click Detach.
Click OK, and the database is detached. But if I just performed those steps, and I
didn’t know where the .MDF file was located, I wouldn’t have a very good idea of how to
reattach that file. So here’s a quick tip.
If you click “New Query” while selecting a database (in this case AdventureWorksLT),
you can type sp_helpfile, and then click F5. sp_helpfile will give you the full path to
the location of the .MDF and .LDF files. Then when you detach the database, you’ll
know exactly where to go to look for them. Just as a side note, if we had placed the
original MyDatabase.MDF file in this directory, it would have attached successfully without
running as Administrator, because that data directory for Microsoft
SQL Server has appropriate permissions for my user account.
I hope this video has helped to explain how to attach a database and given you some other
useful advice. Thanks for watching!

100 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *