-> I tried attaching a MDF file without LDF file using below command and it worked fine,

-> The database JBSWiki looks fine.

-> I tried executing below query and got an error,

select * from Table1

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x78812100; actual: 0x7881211c). It occurred during a read of page (1:320) in database ID 6 at offset 0x00000000280000 in file ‘c:\temp\JBSwiki.mdf’. Additional messages in the SQL Server error log or operating system error 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.

-> It is clear from the message that the database is corrupted. Let me execute a DBCC Checkdb and validate the results,

use [master]
GO
dbcc checkdb ('JBSWiki')

Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 677577452, index ID 1, partition ID 72057594043760640, alloc unit ID 72057594050248704 (type In-row data), page (1:320). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.

Msg 8928, Level 16, State 1, Line 3
Object ID 677577452, index ID 1, partition ID 72057594043760640, alloc unit ID 72057594050248704 (type In-row data): Page (1:320) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 3

Table error: Object ID 677577452, index ID 1, partition ID 72057594043760640, alloc unit ID 72057594050248704 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:320) and previous child (0:0), but they were not encountered.

CHECKDB found 0 allocation errors and 3 consistency errors in database ‘JBSWiki’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (JBSWiki).

-> From the error message we can see that the corruption is on Object id 677577452 and the minimum repair level for correcting this issue using DBCC checkdb is repair_allow_data_loss. This means there will be data loss.

-> Lets check what object id 677577452 is using below query,

use [JBSWiki]
GO
select object_name(677577452 ) as 'Corrupted Table Name'

-> So we know that there is an issue on Table1 object and from the DBCC Checkdb results we can see that page 320 in File id 1 is experiencing issue. Data loss is not an option in this case.

-> The only option left is to restore the database with a valid backup. But the bad news is that there are no backups.

-> SQL Server Database recovery tools is something we can check as an option. In my case, I tried “Stellar Repair for MS SQL“.

-> Before using Stellar Repair for MS SQL tool, it is better to backup the MDF and LDF file for safety.

-> Once done make another copy of the files to use it with Stellar Repair for MS SQL tool. In my case I have placed the file in location F:\Debug,

-> Open “Stellar Repair for MS SQL” and click on “Select database”,

-> Select the corrupted MDF file and click on Repair,

-> Repair completed,

-> Once repaired, Click on Save,

-> Processing this new database is going to take time depending on the database size. The time spent is definitely worth it, as I was able to recover the whole database.

-> Once the database is recovered. Do thorough testing and make sure the database looks alright.

-> I executed DBCC CHECKDB on the new database recovered by Stellar Repair for MS SQL and it came out clean.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.