Tip:
Highlight text to annotate it
X
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!