none
Trying to attach MDF file without LDF (broken)

    Question

  • Hi. 

    I am trying to attach 500MB MDF file using SQL Express 2008 after a suspect status of the database. The first action I taked was to stop SQL server, make a backup and restart it. The problem was that I couldn't copy the LDF file because of CRC error. So I have now only the MDF file, I tried to to attach using this query but returned me error 5120:

    sp_attach_single_file_db @dbname= 'HostelNetTPV', @physname= 'C:\temp\Juanito\HostelNetTPV.mdf'


    And this is the result:

    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

    Is there any way to recover the data?

    Thanks

    Friday, May 18, 2012 9:41 AM

Answers

  • See this link

    http://sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, May 18, 2012 10:03 AM

All replies

  • to recover data obviously you need tran log, to attach database with out tran log see this link

    http://www.mssqltips.com/sqlservertip/1894/attach-a-sql-server-database-with-a-missing-transaction-log-file/

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Friday, May 18, 2012 9:52 AM
  • Same error message: 

    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

    Any other suggestion?

    Thanks.

    Friday, May 18, 2012 10:01 AM
  • See this link

    http://sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, May 18, 2012 10:03 AM
  • Check this blog http://database-recovery-software.blogspot.in/2011/05/restore-ms-sql-database-without.html, It will help you.


    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, May 21, 2012 5:16 AM
  • Do not you have good last backup? If so, I see the only way to call MS support to get the data from .MDF file

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, May 21, 2012 5:44 AM
  • Another option for you is "try any 3rd party" MS gold certified partner's product like Stellar SQL database recovery software. It is able to recover data from .MDF file & supports SQL 2008 R2, 2008, 2005 & 2000.

    Find related discussion regarding Stellar Phoenix software: http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/0e65a18a-9478-49cd-9d95-f43f4abac2f4


    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Tuesday, May 22, 2012 3:06 AM
  • Bunghi, please read this url provided by v.vt carefully and understand the implications. You *should* really perform a backup and restore but if for whatever reason you do not have backups then this is your last resort.

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you

    Tuesday, May 22, 2012 10:11 AM