Post reply

Issue with 2012 AdventureWorks database attach

  • June 18, 2012 at 9:17 am

    #260028

    Hi Everyone,

    Hope all is well.

    I was trying to attach the Adventureworks2012 data file without a .ldf and was getting the following error:

    Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)

    So I scripted it out and tried to do the same from the SSMS 2012 and now I got a different error as shown below:

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect.

    Msg 5123, Level 16, State 1, Line 1

    CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\AdventureWorks2012_log.ldf'.

    Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'AdventureWorks2012'. CREATE DATABASE is aborted.

    I am a domain admin and I have the data file located on C:\ drive of my server. After going through a couple of threads I was able to attach the data file without the ldf file. All I did was moved the .mdf to a different folder instead of trying to attach it directly from the root. Can someone tell me what was the issue here. Are we not supposed to attach files from the root? :unsure:

    Thanks for your inputs

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • June 18, 2012 at 9:35 am

    #1502300

    Have to ask first, why are you trying to attach the mdf file without its associated ldf file?

  • June 18, 2012 at 10:57 am

    #1502334

    Lynn Pettis (6/18/2012)

    Have to ask first, why are you trying to attach the mdf file without its associated ldf file?

    That's how you get the 2012 OLTP AdventureWorks2012 sample database. It's just an MDF file.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 18, 2012 at 11:00 am

    #1502335

    Just tried it and it worked. Assuming you're using the gui, delete the log file that it automatically tries to create. Remove that, then attach just the .MDF and it should work.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 18, 2012 at 11:23 am

    #1502349

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Have to ask first, why are you trying to attach the mdf file without its associated ldf file?

    That's how you get the 2012 OLTP AdventureWorks2012 sample database. It's just an MDF file.

    I'm sorry, that's messed up.

  • June 18, 2012 at 11:41 am

    #1502361

    Lynn Pettis (6/18/2012)

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Have to ask first, why are you trying to attach the mdf file without its associated ldf file?

    That's how you get the 2012 OLTP AdventureWorks2012 sample database. It's just an MDF file.

    I'm sorry, that's messed up.

    Really? I love it. It makes it so much easier to throw up a copy of AdventureWorks.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 18, 2012 at 11:44 am

    #1502364

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Have to ask first, why are you trying to attach the mdf file without its associated ldf file?

    That's how you get the 2012 OLTP AdventureWorks2012 sample database. It's just an MDF file.

    I'm sorry, that's messed up.

    Really? I love it. It makes it so much easier to throw up a copy of AdventureWorks.

    Seems to me that it sends the wrong message to people. Yes, it may work in this case, but how many times have we seen this done on the forums to recreate the log file and it trashes the databases because the rules weren't followed when doing it.

  • June 18, 2012 at 11:58 am

    #1502371

    Lynn Pettis (6/18/2012)

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Have to ask first, why are you trying to attach the mdf file without its associated ldf file?

    That's how you get the 2012 OLTP AdventureWorks2012 sample database. It's just an MDF file.

    I'm sorry, that's messed up.

    Really? I love it. It makes it so much easier to throw up a copy of AdventureWorks.

    Seems to me that it sends the wrong message to people. Yes, it may work in this case, but how many times have we seen this done on the forums to recreate the log file and it trashes the databases because the rules weren't followed when doing it.

    Not trying to argue, but I'll point out that the people who can't properly detach a database are the same ones who can't properly back them up or restore them. Many mechanisms are wonderful in SQL Server when used appropriately... or dig really giant holes when used inappropriately.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 18, 2012 at 12:04 pm

    #1502377

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Grant Fritchey (6/18/2012)

    Lynn Pettis (6/18/2012)

    Have to ask first, why are you trying to attach the mdf file without its associated ldf file?

    That's how you get the 2012 OLTP AdventureWorks2012 sample database. It's just an MDF file.

    I'm sorry, that's messed up.

    Really? I love it. It makes it so much easier to throw up a copy of AdventureWorks.

    Seems to me that it sends the wrong message to people. Yes, it may work in this case, but how many times have we seen this done on the forums to recreate the log file and it trashes the databases because the rules weren't followed when doing it.

    Not trying to argue, but I'll point out that the people who can't properly detach a database are the same ones who can't properly back them up or restore them. Many mechanisms are wonderful in SQL Server when used appropriately... or dig really giant holes when used inappropriately.

    Not trying to argue either, just seems messed up to me.

  • June 18, 2012 at 12:18 pm

    #1502385

    Just tried it and it worked. Assuming you're using the gui, delete the log file that it automatically tries to create. Remove that, then attach just the .MDF and it should work.

    Thanks for your reply Grant. I removed the ldf file and tried to attach the mdf file alone with the .mdf file in c:\ drive. I still get same error. If I create a new folder and put the mdf file in that folder and do an attach then it works. I still dont know the reason behind this. But AFAIK the sql server service account is a domain admin like my account and I wonder why it says Access is denied (if i try to attach it through a script from SSMS).

    Thanks again.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • June 18, 2012 at 12:33 pm

    #1502394

    Sapen (6/18/2012)

    Just tried it and it worked. Assuming you're using the gui, delete the log file that it automatically tries to create. Remove that, then attach just the .MDF and it should work.

    Thanks for your reply Grant. I removed the ldf file and tried to attach the mdf file alone with the .mdf file in c:\ drive. I still get same error. If I create a new folder and put the mdf file in that folder and do an attach then it works. I still dont know the reason behind this. But AFAIK the sql server service account is a domain admin like my account and I wonder why it says Access is denied (if i try to attach it through a script from SSMS).

    Thanks again.

    May be a security setting on the machine. I'm testing on my laptop, not a dev server.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 18, 2012 at 6:10 pm

    #1502529

    Had similar problems and spent couple of hours with different permutations and combinations, finally realizing that my log file need to be rebuild/created for the data (.mdf) file using athe T_SQL script and NOT THROUGH GUI... Use "Create database" statement with "for ATTACH_REBUILD_LOG".

  • October 17, 2012 at 9:05 am

    #1550081

    I had this issue and all the solutions online was kind of misleading to my issue. I have the solution here.

    http://dotnet-programming-solutions.blogspot.com/2012/10/attach-database-encountered-operating.html

    The solution was to Run SSMS as Administrator.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply