2

I had a database in status SUSPECT (No items). I have not Backup. I've only .mdf and .ldf files. After reading all answer regarding this topic, I executed all commands to fix this status, but without success. I also executed the following commands for recovering my database : 

sp_detach_db 'Mydb'

and the following message:

"Successfully detached database 'Mydb'." )

then executed sp_attach_db @dbname = N'Mydb', @filename1 = N'E:\MSSQL7\Data\Mydb_Data.MDF', @filename2 = N'E:\MSSQL7\Data\Mydb_Log.LDF'

and the following error message :

("Server: Msg 823, Level 24, State 1, Line 1 I/O error 38(Reached end of file.) detected during read of BUF pointer = 0x11c01980, page ptr = 0x1f6bc000, pageid = (0x1:0x7bea01), dbid = 7, status = 0x801, file = E:\MSSQL7\Data\Mydb_Data.MDF. Connection Broken "

I executed command with the Service Manager stopped (I don't know if it's important) Please I'm desperate. What can I do to recover my db. I'm not expert in SQL Server and it would be appreciate if the answers were very clear

Shanky
19.2k4 gold badges38 silver badges58 bronze badges
asked Jul 14, 2016 at 12:52
4
  • First do a copy of this database , and after that you can work safely on files with dbcc check and other database diagnostic tools . Commented Jul 14, 2016 at 12:53
  • Looks like an issue with the command you used. What version of SQL Server are you using? When is your latest backup? Commented Jul 14, 2016 at 13:03
  • 6
    You did the biggest mistake of your life by detaching the database. One should never do this for suspect database you almost loose all chance to recover it. I suggest you read Paul Randal Blog Hack Attach Damaged Database. Follow the steps mentioned Commented Jul 14, 2016 at 13:13
  • You can also read this blog, experts-exchange.com/articles/31591/… Commented Apr 9, 2021 at 9:26

1 Answer 1

1

If your database is marked suspect and you are desperate, then I'll advice you to set the status to that database, like:

update master.dbo.sysdatabases set status = 20 where name = 'YourDatabaseName' Very sorry that you have no backup. Also have a look this Recommended actions http://www.techrepublic.com/forums/discussions/it-seems-our-sql-database-is-corrupted/

answered Jul 20, 2016 at 16:16
1
  • I am very grateful for your advice. Thank you very much! The issue has been resolved.) Commented Jul 21, 2016 at 21:56

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.