Let’s say someone wants to give you their SQL Server database. Maybe they want you to manage it, troubleshoot it, or learn from it. So they hand you a .MDF file and .LDF file and tell you that you need SQL server 2008 R2 to open them. So you install SQL Server 2008 R2 Express (which, to your relief, is free). You open SQL Server Management Studio(SSMS), and you try to open the .MDF file. You get the following error:
The Solution:
.MDF files are SQL Server database files and .LDF files are the associated log files. But you can’t really “open” them. Instead, you have to “attach” to them. Once you attach, you’ll see the database in the object explorer of SSMS.
If you’re thinking, “Why wasn’t the Backup and Restore process used instead of passing around these database files?” It’s a valid question, but it doesn’t help the reader staring at .MDF and .LDF files with no clue how to access them. However, if you need to move a database around, you should do a search on “backup restore attach detach sql server” (without the quotes) in your favorite search engine. You’ll get lots of opinions to help you decide the best approach. |
How to Attach in a Perfect World:
There’s a couple of different methods. I’ll give you the “easy” way through SQL Server Management Studio (SSMS) and then point you to other methods using T-SQL queries.
- Launch SSMS.
- Connect to your SQL Server Instance.
- Right-click on Databases in the Object Explorer.
- Click Attach.
- In the Attach Databases window, click the Add button.
- Navigate to the directory containing the .MDF and .LDF files.
- Select the .MDF file, and press OK.
- Press OK again to attach the database.
You should see the database appear in the Databases node (press F5 if you don’t). You can now explore the data using SSMS. These same steps are documented in the MSDN here. I’m just summarizing to make it easier for you.
There are two ways to accomplish the same thing in a T-SQL script. First, you can use the sp_attach_db stored procedure. This is shown here. Or you can use the CREATE DATABASE command with the FOR ATTACH argument.
So, the World isn’t Perfect
If your database is successfully attached, then stop reading and go use it. But if you see any errors or unexpected behaviors, I’ll try to share some troubleshooting suggestions.
First, you may get a generic error like the following..
You might be asking, “Where’s the hyperlink?” I’ll show you in the next screenshot (circled in red), and it will also be our first example.
1. Access Denied:
This access denied error could have a few different causes. Basically, you don’t have the required access to the .MDF or .LDF files. This can happen if you get the file from someone else. When they detach the database file, the file permissions are changed to give only that user full control. Also, even if you see that the file has full control for the Administrators group, it may not be enough. Remember in Windows 7/Vista, those permissions are often only realized when you’re running an application as an Administrator.
So here are several choices to fix it:
- The easiest solution is to close SSMS and then run it as an Administrator. Perform the attach as an Administrator, and it’s likely going to work.
- Another solution is to explicitly grant full control to the .MDF and .LDF files to your user account. This can be done by right-clicking the files, selecting Properties, and modifying the Security tab.
- A final solution is to copy the files to the default directory for your other database files. To find out what that is, you can use the sp_helpfile procedure in SSMS. On my machine it is: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA. By copying the files to this directory, they automatically get permissions applied that will allow the attach to succeed.
2. Access Denied Variation 2
There is another variation of the Access Denied message that has a simple solution:
This error means that the file is already opened exclusively by another application. The most likely cause is that this database file is already attached to an instance of SQL Server. Double-check your list of databases to see whether it is already in the list. It’s also possible for applications to use .MDF files directly with a feature called User Instances. If an application is using a .MDF file in this way, then it would have to close before you could attach the to that database file.
3. Database is Read-only
This is not so much an error as an undesired result. When you load the database, it is in a read-only state. You can tell by the gray shading of the databasee icon (not to mention the “Read-Only” label next to it).
The most probable cause is that the .MDF file you attached to is read-only. Just right-click on the .MDF and .LDF files, select properties, and then uncheck the Read-only check box.
4. Unable to Downgrade
The next error happens when you try to attach a database of a higher version of SQL Server (like SQL Server 2008 R2) to a SQL Server Instance of a lower version (like SQL Server 2005).
This has never been supported. You can attach a database of an older version of SQL Server to a newer version, but you can’t go the other direction. If you want to verify the version of SQL Server you’re running, see this post.
A Parting Word on Detach:
This post is just covering the scenarios of, “How do I open this .MDF file” and “Oh, I have to attach it, why am I getting errors?”. But how were these files obtained in the first place? The answer is that there is a corresponding Detach feature in SQL Server. You can find it by right-clicking on the database in SSMS, selecting tasks, and looking for Detach. Before you do this, though, run the sp_helpfile procedure in a query window in the context of your database. This will tell you *where* the detached .MDF file will be located. After you’ve detached the file, you can attach it to any SQL Server instance. Although this post was about Attach, I think it’s worth mentioning this for completeness.
Thanks… it is very useful for me…
And i can do me work again…
i have installed sql server 2005, when i open a .mdf file it says,
“make sure that the application file type(.mdf ) is installed”
wat to do? plz help
and whenrver i add a .mdf file it says
downgrade path is not supported
Are you using SQL Server Management Studio? If the .MDF file was created in SQL Server 2008, for example, then you can’t attach it in SQL Server 2005. You might be running into that problem. You could try uninstalling 2005 and installing 2008 instead. Here is a video that shows how to get the latest version of SQL, and then just follow the steps to attach. How to Install SQL Server 2008 R2 Express.
This post is just covering the scenarios of, “How do I open this .MDF file” and “Oh, I have to attach it, why am I getting errors?”.
?
i will attach a new database in.mdf file its show error is microsoft sql server Error 5171 how is solve the error message
I have also the same error, can anyone advice with the solution? Thanks
sir
your advise are good and very informative
the issue m facing is that
i made a certain tables in sql 2005 bt after that my window got currpted and i eventully had to reinstall my window and i lost my previous data in sql…….
bt what i have is a database folder that consisted of those table ,the folder was used in an asp.net application…..
now i have reinstall 2005 sql
sir kindly tell me how can i get my previous sql 2005 tables again…….
and i have read ur post i m also getting the errors on attaching the mdf file……..
the error says
“TITLE: Microsoft SQL Server Management Studio Express
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
——————————
ADDITIONAL INFORMATION:
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.). (Microsoft SQL Server, Error: 17750)
sir kindly help me in reconnecting to my old 2005 sql tables……..
i shall be waiting for ur informative reply…………
Had the Same problem and followed your Solution, You are Awesome
Thanks,
Now I can go back to goofing off.
FxM 🙂
thanku sooooo muchh
I have a mdf and ldf file created in visual studio 2005 with SQL server 2000 now i want to open that files in visual studio 2008 with SQL server 2005 is it possible to use that files in SQL server 2005 ???? Plz help me on this i will be very thankful to u plz
I have not tried going from SQL Server 2000, but it should work according to http://msdn.microsoft.com/en-us/library/ms189625.aspx.
how can i find .mdf file in my sqlserver………….
.mdf file presented in your programs file……..
Sir thanxx for this generous help …
Perfect solution..! Thank you so much. This post was a big help for me.
thanks its rally helpful………………………..
You save my life! (my programmer life of course), spend hours and hours under pressure from my boss until I found your link, thank you!!
Very needful
thanks
Hi, previously, I was able to attach the .mdf file in SSMS 2008 succesfully, but after I closed the program and wanted to open the database again, it is said that error 3415 occured. What do I have to do to fix the problem? Thank you for your help
This helped me a lot..Thanks
Great job. Thanks
Thanks You…. 🙂
I accidentally changed the properties of the .mdf file to open with notepad.. i tried all your methods to open the .mdf database yet its of no use. Any suggestions? I keep getting the error message of the first type you mentioned…
it’s really help me, thank you 😀
thank you very much, updated to win8 consumer preview & it isn’t supported @ by my support desk at school (don’t know why…) really helped thanks!!!
Brilliant!
hello all I have a problem with ldf log file I need to maximize the the size of it how??? can anyone help me please??
Thanks Jason…your first given suggestion is working fine.
Hello,
I have a little problem… I use (Windows XP) not (Windows 7 or Vista)… how to grant full control permission in Windows xp?
Somebody please help me also can send answer to Syid98@gmail.com
tanks
Hi how are you, Mr. Jason.
I am not on Mdf file. Figure 4 is pretty difficult.
Please help if you can.
Another problem I have is that I do not speak much English.
I’m from Iran.
Thanks for everything
Thanks Sir, it is very helpfull to me………………
i have installed sql server 2005, when i open a .mdf file it says,
“make sure that the application file type(.mdf ) is installed”
wat to do? plz help……….it is created also in 2005……..
When trying to attach a new .mdf I get an error. The file is compressed…It must be decompressed
I’m using SSMS 2008 R2. Any ideas how to fix this issue?
Here is the link with SQL server Management studio it may help any one
http://www.reddyinfosoft.blogspot.in/2012/06/attach-mdf-file-to-sql-server-using-sql.html
Thanks, useful, useful post!
thanks for reply…………..
CREATE DATABASE name_of_database_which_u_want
ON (FILENAME = ‘E:\BBDB1.mdf’),
(FILENAME = ‘E:\BBDB1_log.ldf’)
FOR ATTACH;
========================================
this this click on execute if above methods are failed as in my case
this will defineltly work i see it on sql site
“Process cannot open the file because it is in use by another process”
How do I find which application is using the file and disable it?
I closed down my business but neglected to export MY DATA, accumulated over 5 years, to Excel or some other generic format. The vendor wants me to re-license the software if I want to open the application. Their shortest term is 12 months! I am not in the business any longer and I have no use for the application, I merely want to get MY DATA.
My software licensor says that someone “with a knowledge of MS SQL” can open the files. I am a novice to SSMS but I’ve managed to get as far as attaching the file to create a new database but then I get the above error message.
So, do you have the database in your list? Or when you try to attach the database, you get the error that another application is using the file? If you can’t attach the database because it is in-use, then you could try to figure it out with a tool called “Process Explorer” from the TechNet.microsoft.com website. You can do a search to find it.
I created database in Sql server2008 but i am nont able to attach that one in Sql server2008r2
I could not resist commenting. Very well
written!
thanks …i load file from different location and gave full access to mdf file by right clicking….thnx
Hi Jason,
Thanks for your nice explanation of opening a MDF file. Here is one of the scenarios I ran into as you mentioned ( when I try to attach in SSMS) , expect I error no it little different.
CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘D:\SID_DNN\App_Data\Database.mdf’. (.Net SqlClient Data Provider)
This is SS 2008-R2 version. I was “Attaching Database” as Server\Administrator. I need to view the database schema of this database and query few tables to see how the data is stored to be enable me to do reporting. This database this accessed by a live DNN module in our website. How do you suggest I view the schema and query the tables in this databases ? Thanks for your help in advance.
Adding to above question, can I explore the database in Read-Only mode in SSMS since it is being used by another process. thanks!
From Jason suggestions to stop the process accessing the .MDF and then “Attach” to SSMS. – Once “Attached” I should be able to view/explore the db – correct?
But then , if we can resume the DNN process to access the db as before – will I run into any hurdles. In other words can one explore the database in SSMS while the database is in use by an application?
This was great, works like a charm, Many thanks
Very helpful tutorial… Work perfectly… Thank you very much… regards from Indonesia 😀
thanks..
I received the following error with the attach “Directory lookup for the file “d:\data\MSSQL\data\Metric_info_log.ldf” failed with the operating system error 21 (the device is not ready). (Microsoft WQL Server, Erorr; 5133)
How do i correc this? I gave all users full access and also access SQL Server studio in an Administrator role. I don’t think the error received pertains to that of a permission error. I’ve also added the .mdf file to the SQL server data path as mentioned as well… Nothing.
Any info that anyone has to assist in solving this issue would be great.
Thanks in advance,
LJ
Running SSMS as administrator and changing .mdf file permissions to full has really helped me.
Cheers
Thank u Sir.
It is very usefull information that solve my all problems.
Hello Roth. Your post very useful. Thanks! This is very helpful information in this post.
I’ve recived another error and cannot understand what problem is. Can you help me? I’m absolutely new in SQL Server.
I’ve insatlled SQL Server 2012 Express. My operating system is Windows Vista 32-bit. In addition, Microsoft SQL Server Management Studio was run with administrator’s permission.
I tried to attach the next database – PUBS.MDF. This database is stored here http://www.microsoft.com/en-us/download/details.aspx?id=23654 . But, I recieved the next message when I tried to attach database:
Attach database failed for Server ‘GAMBLER\ANTONSQLSERVER’. (Microsoft.Sql.Server.Smo)
Additional information:
– An exception occured while executing a Transact-SQL statement or batch. (Microsoft.Sql.Server.ConnectionInfo)
– Database ‘pubs’ cannot be upgraded because its non-release version (539) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database. Could not open new database ‘pubs’. CREATE DATABASE is aborted. Microsoft SQL Server, Error: 950).
And what does “non-release” version mean? I also tried to understand, why my version os sqlservr.exe is incimpatible, but I can’t understand. The ‘Pubs’ database was created on SQL Server 2000, but my version more new. What’s problem?
thanks, it worked perfectly.
Superb. Thanking you very much. It really helped.
Thank you.. It helped me
SQL User`s if any case your SQL server database is corrupted and damaged then don’t , try SQL Recovery Software which is finest software for recovery of Corrupted SQL server MDF file. For more info then read this article: http://www.articlesbase.com/software-articles/sql-database-recovery-assured-with-technical-intellect-application-6538100.html
Thank you very much sir . it helpled me and now am able to attach mdf.
Thank You for providing helpful info.
Sir,
Thank you for sharing a information with us. I am a student and i am preparing my collage project. I had created one web application which also have database. Now problem is when i am trying to attach my MDF and LDF file in a hosting server its giving me an error “access denied”.
could you please give me some advise why this error is appearing.
You can open ldf files with http://www.apexsql.com/sql_tools_log.aspx
nice
good one
it;s really works thanx u
I’m currently doing a group project and we have our main database but when we try to send or open updates to the .mdf or .ldf it will not open and gives an error message saying the file application is not found. Could you please assist?
Thanks a lot 🙂
It was really helpful
thank you…. it really works .. 🙂
No matter if some one searches for his required thing, therefore he/she wants to be available that in detail, so
that thing is maintained over here.
thanks a lot …it saved me lot of work 🙂
Exceptional post however , I was wanting to
know if you could write a litte more on this topic? I’d be very thankful if you could elaborate a little bit more. Appreciate it!
Rather useful, look frontward to returning.
Thanx its really helpfull…….can you please tell me
very userful informations
Thanks
Pandiarajanr
🙂 IT WORKS!
thanks for tips bcz its very useful me
Fantastic Solutions !!
thank you so so so so much you helped me
bonne continuation
Thanka alot sir…
i made a certain tables in sql 2005 bt after that my window got currpted and i eventully had to reinstall my window and i lost my previous data in sql…….
bt what i have is a database folder that consisted of those table ,the folder was used in an asp.net application…..
now i have reinstall 2005 sql
sir kindly tell me how can i get my previous sql 2005 tables again…….
and i have read ur post i m also getting the errors on attaching the mdf file……..
the error says
“TITLE: Microsoft SQL Server Management Studio Express
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
——————————
ADDITIONAL INFORMATION:
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.). (Microsoft SQL Server, Error: 17750)
thanks a lot dear, I’ve been trying to attach a database for a long time but, i was getting errors, now that i run SSMS as administrator i can successfully attach it.
thanks again for your information.
thanks for your help..Its helpful for me…For those who cant attach database….just copy the mdf into sql/data folder in your c drive….its work…you attached back from there
I need one help.Can u tell me how to reduce the growth of mdl file in database?
Xxx Fille Vierge La Premiere Foi streaming xxx, Xxx Fille Vierge
La Premiere Foi movie xxx, Xxx Fille Vierge
La Premiere Foi movie porno
2 En entire xxx Search Q WWW DUBI XXX COM free xxx, 2 En entire
xxx Search Q WWW DUBI XXX COM movie porn, 2 En entire xxx Search
Q WWW DUBI XXX COM video xxx
thanks its so helpfull
Couldn’t stop myself for praising for the great job done by Mr. Jason Roth…
Hats off to u SIR 🙂 (y)
Sir, i have taken a project from someone else… It is complete but it is not working due to some troubleshoot showing message
“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”
I have two files of format .mdf and ,ldf in the folder of that project which i think i should connect them to the project to proceed it with all the steps defined in the project……. the project has been made in visual studio 2008 and sql server management studio 2005 has been used for making the project….. 😦
please help me do it the right way and make my project working….
سلام از شما بابت مطالب خوبتون تشکر می کنم
پیروز باشید
Thanks for solution.
hi thank you so much it was very very useful for me thank u so much…..just now i start learn java last 2 weeks i try install this database but i faced lots of problem but it was clear thank you…
Sir , how can we restore .mdf where backup from different system and restore same backup on different system.
eg.
backup location of first system is
C:\users\user1\desktop\…..\App_Data\database.mdf
now we restore that data on other pc
C:\users\ABC\desktop\…..\(any directory)\database.mdf
Reblogged this on Life is short. I wanna make mine more fun. and commented:
This help me a lot.
Thank you very much
you saved me
Thanks Sir for your very helpful post
Thank you much, saved my life 🙂
Thanks for info… First time I found solutions to all the possible errors in a single article..!!!
finally something useful over the internet for this problem…
copy for all topic
PLS MDF HEADER OR IN HEXADECIMAL NUMBER PLS TELL ME
thanks, very helpfull
You can also take the help of free SQL MDF Viewer Tool to open and view mdf file data without SQL Server: http://www.sqlmdfviewer.org/
Thanks a lot 😀
Thank you So much You saved my time ! I was in trouble for the last 24 hours. your support is so important almost you included all errors which possibly occur !
You can also take the help of this link to attach mdf file using sql server management studio and transact-sql: http://www.sqlrecoverysoftware.net/blog/attach-sql-database.html
Very useful 🙂 Thanks
thanks..
thanks a lot..
It was a nice article. It worked for me..
Thanks
Perfect!! It’s run OK. Thanks!
Thank you very much. This saved me a lot of time and nerves.
Thank you so much (y)
How to Attached .mdf of SQL Server 2014 to in SQL Server 2012
Thanks a lot. It was very useful to me.
Its help full for me thanks….
Just passing by to tell you I’ve had this same issue and running SSMS as Administrator did the trick. Many thanks for this! (running SSMS 2012 and trying to restore the AdventureWorks2008LT.MDF database)
Thanks man, i almost gave up until i ran mssms as administrator, it was like magic.
Very helpful! Thanks for posting this.
You have no idea of the enormity of your help! Thank you!
Thanks a lot
It’s a precious article, very helpful.
Thanks again
change from security + add (anyone user) + all permition
This post helped me today, thanks Jason!
Kudos to the author… Very intuitively explained. You are a life saver brother…