Post reply

Failure Attaching MSSQL 2000 Database

  • September 18, 2007 at 5:24 pm

    #120490

    Hi,

    I'm having problem of attaching the mssql 2000 database.

    My customer database was in suspect mode so they detached

    the database with errors. Later they restarted the server

    and the database was no longer can be seen in the Enterprise Manager.

    They tried to attach the database using the EM but failed due to corrupted ldf file.

    I gave them the below command to attach the mdf file only and it returned an error.

    exec sp_attach_single_file_db @dbname= 'flxhrmsq',

    @physname = 'd:\sqldatabase\flxhrmsq_data.mdf'

    Server: Msg 3624, Level 20, State 1, Line 1

     

    Location:  recovery.c:2021

    Expression:  curr->GetOpCode () != LOP_NULL

    SPID:   52

    Process ID:  328

    Connection Broken

    They can't restore the old backup because all the while they backup the wrong database. I need to re-solve this ASAP.

  • September 23, 2007 at 12:02 pm

    #735498

    The problem is that they detached the database while it was suspect. Suspect means that recovery could not run for some reason. When you attach a database that has not had recovery successfully run, SQL server will not attach the database unless recovery completes.

    So - the only way to get around this is to use the fake database hack to attach the database. Then you're going to need to put the database into emergency mode, rebuild its transaction log and run a full repair - losing a bunch of data as you do that. I recommend that you call Product Support to help you through this, or upgrade the database to SQL 2005 and take advantage of emergency mode repair that I added.

    See these blog posts for more info:

    http://www.sqlskills.com/blogs/paul/2007/09/13/CommonBadAdviceAroundDisasterRecovery.aspx

    http://www.sqlskills.com/blogs/paul/2007/09/15/CorruptionLastResortsThatPeopleTryFirst.aspx

    And one I did on emergency mode repair while at MSFT:

    http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx

    Let me know how you get on.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Viewing 2 posts - 1 through 1 (of 1 total)

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