Hi,

I've my database, testdb, ended up in Suspect state. The SQL log shows " I/O error 38(Reached the end of the file.) detected during read at offset xxxxxxxxxx in file '<path>\testdb_Data.MDF'" during recovery. I do not have backup to restore the database from. So to run DBCC CHECKDB, I tried to put the database in emergency(bypass recovery) mode using

update sysdatabases set status = 32768 where name = 'testdb'

DBCC CHECKDB showed some allocation and consistency errors and suggested "repair_allow_data_loss" as minimum repair level.

Now to run

DBCC CHECKDB('testdb', repair_allow_data_loss)

I've to put database in SINGLE USER mode. For that I started SQL server by command

sqlservr.exe -c -m

Now when I try to run DBCC CHECKDB with repair option it says "Attempt to BEGIN TRANSACTION in database 'testdb' failed because database is in BYPASS RECOVERY mode."

So it seems I need to change the status of database such that it will allow me to repair it. If I try to reset the status, the database again goes in Suspect state and it seems DBCC commands don't run on database in Suspect state. Does anybody know how to recover the database in this state? Is there any other way to repair it?

Thanks in advance,
Yojana

If you put a database into bypass recovery mode, it probably isn't worth trying to repair. I'd strongly suggest that you simply copy the data out of that database into a new one, vigorously check the logical consistancy of the tables (are all FK values valid, are any rows obviously missing, does the data survive basic business integrity checks, etc), then discard the original database.

Trying to fix one of those is a lot like trying to fix a bus after it goes off a cliff... It can be done, it can be made to look good if you are willing to work hard enough, but it will never be "right" again.

-PatP

Agree with Pat. I fyou have a good backup, restore.

I'm relatively new to SQL. Can you please provide me steps for copy the data out of this database to new one?

Thanks,
Yojana