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

This video shows how to attach a database in SQL Server 2008 R2. It covers a common Access Denied error and provides solutions. At the end, it briefly covers the Detach mechanism and the sp_helpfile procedure. More information can be found on this subject at: http://learningsqlserver.wordpress.com

---

Closed Caption:

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!