Blog reader Donald Crowther has posted following comment. I have not tested this solution and when I tried to test it, it did not work for me. However, I have received email from two of my Jr. DBA who have done experiment about this and they are suggesting it works.
If you have tried everything and you have given up to find solution. Try following suggestion. Make sure you have taken backup of your physical file and also this exercise you do at your own risk.
ALTER DATABASE test
SET emergency
GO
ALTER DATABASE test
SET single_user
GO
DBCC checkdb (test, repair_allow_data_loss)
ALTER DATABASE test
SET multi_user
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Dear Pinal Deve,
I have a backup from a customer, and i can restore it with out a problem, but the problem is i could not query the database, and it is giving following errors. The customer says he had a power failure.
1. Following Error displayed when querying user table
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
2. Following Error displayed after lists some records when querying table SysObjects
Server: Msg 5242, Level 22, State 1, Line 1
An inconsistency was detected during an internal operation in database ‘MBBalkh'(ID:33) on page (1:204).
Please contact technical support. Reference number 6.
3. When i try to repair using the above command given by Donald Crowther, after processing 45 minutes it is giving the following error.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Any help recovering my database ?
Best regards,
Seenivasan
This script worked well for me when restoring a WSS 3.0 content database with only the mdf and no ldf.
After running this script i was able to browse the data and attach it to a SharePoint site.
Thanks.
This worked great. Thank you you saved me.
Pinal Dave,
I have an sql 2005 express db that has been corrupted. The end user has no backups so I’ve tried using DBCC checkdb and I get the following error:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x17b8ed1d; actual: 0x79d02a32). It occurred during a read of page (1:1957) in database ID 7 at offset 0x00000000f4a000 in file ‘C:bytedownloadBYTE_SKU.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
During redoing of a logged operation in database ‘BYTE_SKU’, an error occurred at log record ID (15907:16:98). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Could not open new database ‘BYTE_SKU’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)
For help, click:
The dB was originally marked ‘suspect’ but I was able to set to Emergency/Single User. Now I can write Select statements against the tables but still the db is corrupt.
Any suggestions as to what I might try next?
Best Regards, Matt
You r the man….you saved my bacon after a major failure =8-)
This script saved my day, thanks!
Thx this worked fine for me. SAP database can be restored with this script.
Excelent script, thanks a lot for the trick…
you saved my day too !!
Hi,
I have a 2 TB database and the checkdb gave be below output
CHECKDB found 0 allocation errors and 949 consistency errors in database ‘PRD’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PRD ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After running with repair_allow_data_loss, I got below output
CHECKDB found 0 allocation errors and 949 consistency errors in database ‘PRD’.
CHECKDB fixed 0 allocation errors and 902 consistency errors in database ‘PRD’.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (PRD repair_allow_data_loss).
The statement has been terminated.
Please let me know how to repair the database completely. And is there any way to find out which datafile is most corrupted?
Thanks in advance…
Dear Mr. Pinal,
I used above script – SQL SERVER – 2005 – Restore Database Using Corrupt Datafiles (.mdf and .ldf) – Part 2
and restored from the suspect data.
I will be thankfull to you.
how can i recover a correpted sql 2005 database
Great Help…Thanks Dave
Thanks Dave for the useful tips to restore data. However, there are some situations when CHECKDB and DBCC command line tools do not work. In such cases, a third party sql database recovery application may help you to repair and recover data.
One such globally used tool that worked in our case:
Hope it will also help to those who are also facing similar problem.
Thanks again for these wonderful guide.
Smike
After 2 days trying to fix a suspect db glad i found you Pinal.
it works fine
It’s working properly.
After this i take the DB offline and take it to online.