Corrupted Database File

Question

Post reply

Corrupted Database File

Dinendra Nath Dolai

Mr or Mrs. 500

Points: 594
More actions
March 1, 2007 at 1:34 am

#62547

Due to some disaster my database server crashes. It starting but one database has been corrupted as is is not accessible from SQL Server or from other. Is there any way to restore some portion of the database.
I have the MDF file but not the LOG file

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

You might search the web on sp_attach_single_file_db. Make sure you have it backed up first.

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 2

I think it doesn't work out as

Used sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

Thanks ,

Shekhar

MohammedU SSCoach Points: 19644 More actions · Answer 3

You can try using dbcc rubuild_log which is undocumented...

Here is the example of it...

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9c61356ae5a25602/6c8a9e6807f5efed?lnk=st&q=dbcc+rebuild_log&rnum=10&hl=en#6c8a9e6807f5efed

MohammedU
Microsoft SQL Server MVP

icata Hall of Fame Points: 3176 More actions · Answer 4

Unless it is a read only type of database, whatever uncommited transactions you might have had in the log file(s) are gone.

You can try creating a new database with the same name, same files name, detach it, replace the mdf with the original mdf, then attach back the database.

If it works, you will have to deal with the corruption later.

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 5

You could put the database in emergency mode to copy readable data.

In SQL2000 you do this ( http://www.devx.com/vb2themax/Tip/18624 ) :

update sysdatabases

set status = status | -32768

where name = 'MioSuspectDatabase'

In SQL2005:

ALTER DATABASE [dbname] SET EMERGENCY

Once in emergency mode you could read the data and copy to a new database.