open

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



Sharing buttons:

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!