Attaching .mdf & .ldf problmes

  • I have had to re-install SQL Server 2000 Stn Edition and want to re-Attach a DB from the original data. However when I try to attach the DB in enterprise Manager I get the following error:

    The file you have specified is not a valid SQL Server Database file.

    If I try to attach using sp_attach_db I get the following:

    Server: Msg 823, Level 24, State 6, Line 1

    I/O error (torn page) detected during read at offset 0000000000000000 in file 'D:\Microsoft SQL Server\MSSQL\Data\JiaRegData.mdf'.

    Connection Broken

    Due to bad practice my SQL backup has not updated in the last couple of weeks so I'm fast heading up a certain creak with out a certain implement.

    Does anyone have any idea's or am I doomed?

  • Hi MAX

    i have Q,

    1)what edition u have in you system(ent or dev,std)

    2)r u deatached mdf and ldf or what way u get mdf and ldf

    chat me on sponguru@yahoo.com

  • First of all: I suggest that you make good copies of everything somewhere else. It may take several iterations to recover.

    You can try creating a blank database with the same name then

    USE master
    GO
    ALTER DATABASE %DBName% 
       SET TORN_PAGE_DETECTION OFF
    GO
    SP_DETACH_DB %DBName%
    GO
    

    Rename the new DB files to something else and then put the other .mdf file back in and

    sp_attach_single_file_db   @dbname =  '%DBName%' 
        , @physname = 'physical_name'
    GO
    

    If you successfully attach then run

    DBCC CHECKDB 
        ( 'database_name',REPAIR_ALLOW_DATA_LOSS )
          WITH ALL_ERRORMSGS
    

    You may lose some data, but hopefully not a whole lot. Or you may be totally unsuccessful. Everything I glanced at doesn't give much hope.

    Good luck and let us know.

    Some of this is from

    http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20310046.html

    http://www.webservertalk.com/archive132-2004-10-448389.html



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

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

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