Attaching .mdf & .ldf problmes

• Max -205122

  Valued Member

  Points: 55

  More actions

  January 30, 2005 at 12:03 pm

  #163178

  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?

• Ponguru.SreenivasaRao

  Right there with Babe

  Points: 778

  More actions

  January 31, 2005 at 2:09 am

  #539689

  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

• Jim P.

  SSCrazy Eights

  Points: 8725

  More actions

  February 1, 2005 at 7:02 am

  #539923

  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.