none
SQL Server 2008 R2 { An Error occured when attaching database(s) }

    Question

  • Hello Guys!

    I just installed SQL Server 2008 R2 a couple of days ago, on the first day use i can attach database(.mdf) with out any problem.

    on the day 2, i keep getting this error:

    http://img515.imageshack.us/img515/14/1212i.png

     

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details.

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    after clicking the Hyperlink this window open:

    http://img34.imageshack.us/img34/8766/hyperg.png

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Attach database failed for Server 'STARGAZER-PC\SQLSTARMAX'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Unable to open the physical file "E:\New folder\DataBase.MDF". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

     

    in that day i reinstall SQL Server 2008 R2, because I'm so confused. after that i try to attach i still got the same error, what i did is i share the folder where the database is.

    i open SSMS and try to attach and i attached without any error...

    i think this is the 4rth day, untill now everytime i want to attach a database i got this error, and even i try to share the folder where the database is i still got that error.

     

    any advice guys?

     

    Thanks a lot

     

     

    jhules


    if you want to be happy, find something you love to do and do it for free, then find someone to to you for it.
    Thursday, January 13, 2011 9:05 AM

Answers

  • If the Problem is access denied..

     

    Try to RUN your SQL SERVER as Administrator and try to attach. It surely Works... All the best

     

    Thanks,


    Thanks, Aditya Badramraju, ____________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties and confers no rights.
    • Marked as answer by Jhules Thursday, January 13, 2011 2:33 PM
    Thursday, January 13, 2011 12:39 PM

All replies

  • The Error clearly shows that MDF files and LDF files are not matching or LDF file is corrupted.

    You can follow either of the one option below

    1. Restore a recent backup rather attaching the MDF file

    2. Attach  single MDF file without LOG File( Warning: All the log transactions will be gone and new log file will be created)

    EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2008R2',
        @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_Data.mdf';

     


    Thanks, Aditya Badramraju, _______________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties and confers no rights. _______________________________________________________
    Thursday, January 13, 2011 9:46 AM
  • That error indicates

    Unable to open the physical file "E:\New folder\DataBase.MDF". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

    That you do not have permissions are you sure that user who is running SSMS has full NTFS permissions to E:\New Folder directory and to that file?


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Thursday, January 13, 2011 11:11 AM
  • Hi thank you guys for your reply appreciate it.

     

    that's why sometimes i share the folder where the database is and sometimes i can attach the database with out error, but now i already shared the dolder and have a full permission i still get the same error.

     

    Thanks

     

    Jhules


    if you want to be happy, find something you love to do and do it for free, then find someone to to you for it.
    Thursday, January 13, 2011 11:17 AM
  • If the Problem is access denied..

     

    Try to RUN your SQL SERVER as Administrator and try to attach. It surely Works... All the best

     

    Thanks,


    Thanks, Aditya Badramraju, ____________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties and confers no rights.
    • Marked as answer by Jhules Thursday, January 13, 2011 2:33 PM
    Thursday, January 13, 2011 12:39 PM
  • If the Problem is access denied..

     

    Try to RUN your SQL SERVER as Administrator and try to attach. It surely Works... All the best

     

    Thanks,


    Thanks, Aditya Badramraju, ____________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties and confers no rights.


    works like a charm!!!!!!

    Thanks a lot.

     

    Cheers

     

    Jhules


    if you want to be happy, find something you love to do and do it for free, then find someone to to you for it.
    • Proposed as answer by ISMAEL JB Saturday, February 12, 2011 8:02 AM
    Thursday, January 13, 2011 2:33 PM
  • I have faced the same problem but after checking all details of what i have did, i found that the hard disk drive is compressed and i have decompressed then it works fine.
    Saturday, February 12, 2011 8:05 AM
  • Thanks

    I solved the biggest problem :)

    Thursday, February 02, 2012 8:37 PM
  • If the Problem is access denied..

     

    Try to RUN your SQL SERVER as Administrator and try to attach. It surely Works... All the best

     

    Thanks,


    Thanks, Aditya Badramraju, ____________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties and confers no rights.

    Thanks a lot. My problem has been down...

    But why shoud we do it????

    Monday, July 09, 2012 6:14 PM
  • Thanks for your help above, I was able to do it by using EXEC command, but i got this message:

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf" may be incorrect.
    New log file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_Data_log.LDF' was created.

    But luckily I can use all the tables in the database fine, thanks a lot.

    Saturday, July 28, 2012 7:57 PM
  • If the Problem is access denied..

    Try to RUN your SQL SERVER as Administrator and try to attach. It surely Works... All the best

    Thanks,


    Thanks, Aditya Badramraju, ____________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties and confers no rights.

    You are the MAN!

    Thanks.

    • Proposed as answer by ErangaPriyakara Saturday, October 20, 2012 10:37 PM
    Saturday, October 20, 2012 10:37 PM
  • Still i have the same problem.

    Then i tried in Windows Authentication mode, it works fine.



    • Edited by jkumarmcse Sunday, December 16, 2012 3:04 PM
    Sunday, December 16, 2012 3:04 PM
  • Thanks, Authentication mode solved the biggest problem :)

    Dont know why Sql Server Authentication doesnt work, maybe is a bug?

    Monday, December 24, 2012 5:25 PM
  • This may occur when the data(mdf)/log(ldf) files kept on compressed or encrypted folder.


    Thanks & Regards, Karthikeyan

    • Proposed as answer by KarthikNila Wednesday, December 26, 2012 12:32 PM
    Wednesday, December 26, 2012 12:31 PM
  • Friday, January 04, 2013 10:00 AM
  • error when run as admin of SQL Server Mnagemnt studio : http://img515.imageshack.us/img515/14/1212i.png
    Friday, January 04, 2013 10:03 AM
  • Please post the actual error message by clicking the copy icon on the bottom left corner of the message, Make sure the service account has permission to access the data folder.

    Service account can be viewed by Start>Run>Services.msc> SQL Sever (InstanceName) >log On

    In most cases the instance name is MSSQLServer


    Jayanth Kurup - www.enabledbusiness.com

    Friday, January 04, 2013 10:09 AM
  • Dear Aditya Badramraju,

    I have faced the same problem, i followed all your instructions but I am facing the error again and again.

    Please any advice for me from any of you,

    thanks

    Saturday, May 04, 2013 6:30 AM
  • Hi ,

     Please see that you have full control permission on the folder where your data file and log file resides.

    RK on folder go to security tab add your account witch which u have logged in Your Windows server and give full rights to it try attching now also make sure you have sysadmin rights in SQL server database

    This will solve the issue of access denied


    Shashank Singh Soldier..Sir we are surrounded from all sides by enemy.. Major: Good, we can attack in any direction Thats attitude..

    Monday, May 06, 2013 12:51 PM
  • Been trying and wow! It works..

    Thanks All. :)

    Running as administrator and checking the file permission..

    Friday, June 21, 2013 9:29 AM
  • Came to this forum because I had the same issue.  Understood it had something to do with permissions.  Played around with the settings on the security tab of my folders many times trying to figure out what was wrong.  Thought I had everything right but it still didn't work.

    After a period of time I tried to attach my database files again and they did successfully without me changing anything.

    There clearly seems to be something wrong with SQLServer with regard to folder/file permissions, very frustrating and I have seen this since version 2008, currently working with 2012.

    Saturday, July 06, 2013 4:05 AM
  • only change the bath of it to other partation if it on D: move it to F: or any partaion that you have and do attach 

    good luck 

    Thursday, March 06, 2014 2:18 PM
  • I had the same problem with the aw database i changed the directory from C: to E: and then right click on the aw databese select SECURITY tab and grant FULL CONTROL persmission to your local users finally Run SQL Management Studio as Administrator and attach your database.
    Friday, March 28, 2014 8:22 PM
  • This is the best Simple solution for this scenario. I got my issue sorted out. Thanks mate !!

    How do you grant Administrator rights?

    Simple - Go to start -> Microsoft SQL Server 2008R2 -> SQL Server Management Studio -> Right Click here(you see pop up menu with options open, Run as Administrator...etc) -> select that option (Run as Administrator). That's Job done, now you go attach any version of  Adventure works database (2008,2008R2, 2008Lt, 2008R2Lt etc). You will be able to do it with out any problem.

    All the above explaination is given, considering SSMS is not shown  in the Task/Start Menu. If it is already there skip the first 2 steps.!

    Guys, apologies if this method doesn't work which is untrue for this specific problem.

    All the Best. :)

    Cheers.,

    Monday, April 07, 2014 8:56 AM