none
Repair / recover database that is unable to be attached

    Question

  • Hi,

    I had some hardware problems on the database server where I'm working. After restarting the server, I can't attach any database to an SQL Instance. I'm not sure what to do because without being able to attach it, I can't dbcc it. Is there any SQL Server native tool or command that can help? What options do I have? (error message when I try to attach is bellow).

    Thank you

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

    Attach database failed for Server 'LT105996'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.4064.0+((Katmai_SP2_GDR).110225-1353+)&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)

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

    Could not redo log record (585:2858:3), for transaction ID (0:480869), on page (1:302), database 'HYP_PLN' (database ID 9). Page: LSN = (422:770:2), type = 1. Log: OpCode = 4, context 2, PrevPageLSN: (585:2800:3). Restore from a backup of the database, or repair the database.
    During redoing of a logged operation in database 'HYP_PLN', an error occurred at log record ID (585:2858:3). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    Could not open new database 'HYP_PLN'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 3456)

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

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

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

     


    • Edited by Pedro Samuel Monday, November 28, 2011 12:37 PM Title change
    Monday, November 28, 2011 12:36 PM

Answers

All replies

  • Hi,

    there is a workaround documented by Paul Randal at here: http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx

    You may use it with your own risk.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    • Marked as answer by Pedro Samuel Friday, December 2, 2011 2:17 PM
    Monday, November 28, 2011 12:43 PM
  • Do not  you have a last good backup of that db, that will be an ideal solution
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, November 28, 2011 1:04 PM
  • Uri, you are right. Last good backup is always the best option, however if you do not have, this may be an option, but not on production server.
    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Monday, November 28, 2011 1:26 PM
  • The backups were all on the same server and restoring from them also gives errors :( it's like all the file system was damaged.
    Monday, November 28, 2011 2:42 PM
  • Make sure that SQL Server Service account has modify permissions over the physical file on the hard drive that you are trying to attach to a SQL Server instance, and modify permissions also over the folder that contains the file.

    Ref: http://www.sqlcoffee.com/Troubleshooting022.htm


    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, November 29, 2011 5:37 AM
  • Wow, I think you are about to be out of luck.... Try open a case on MS....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 29, 2011 8:19 AM
  • I can't even set the database to Emergency Mode. I think the damage is so great that I have no chance to recover it.

    Is there anyway to just recover the data somehow? There are some tools on the web that show the mdf content and claim they can fix it, and all the demo versions preview the mdf file content but don't allow me to save it.

    Tuesday, November 29, 2011 10:55 AM
  • Hello Samuel, Yes, there are many tools available in the market that claim to recover mdf content. I would like to recommend to a Microsoft Golden Satisfy Partner's Product Stellar Phoenix SQL Recovery software. It shows the preview of corrupt database. To save the corrupt database, you will have to buy the software. All the related information is available @ http://www.stellarsqldatabaserecovery.com/
    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

    • Edited by Markwillium Tuesday, November 29, 2011 11:28 AM
    Tuesday, November 29, 2011 11:23 AM
  • There are several other software also available for SQL Recovery, I would also like to mention here one of them. By SQL Server Repair Toolbox, you can instantly repair damaged MDF file with in few clicks. You can get more details along with a free trial from here http://www.sqlserver.repairtoolbox.com/
    Tuesday, April 29, 2014 12:55 AM