When attaching a .MDF database in SQL Server, you see the following error message:
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x########; actual signature: 0x#######). It occurred during a read of page (#:#) in database ID # at offset ### in file 'xxxx.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.
where 'xxx.mdf' is name of the MDF file being accessed.
Sometimes you the .MDF database may be attached successfully. However, when you try to execute a SQL statement, such as
SELECT * FROM [TestDB].[dbo].[test_table_1]
you will also get the above error message.
Screenshot of error message:
The data in MDF file are stored as pages, each page is 8KB. SQL Server uses two mechanisms to make sure the consistency and integration of the data in the page, that is, checksum or torn page. Both are optional.
If SQL Server finds the torn pages for some of the data pages are invalid, then it will report this error.
You can use our product DataNumen SQL Recovery to recover the data from the corrupt MDF file and solve this error.
Sample corrupt MDF files that will cause the error: