SQL Server Database I/O Consistency Error Disaster Recovery
2
votes
1
answer
88
views
I have a corrupted SQL Database that is inacessible by any means. It is to the point that simple commands, like USE MyDatabase, won't execute.
The error I get is: SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:371; actual 0:0). It occurred during a read of page (1:371) in database ID 5 at offset 0x000000002e6000 in file.
The database got to this situation because this is a corrupted .MDF file saved from a dead HD that is not accessible anymore. So, the .MDF contains the more recent data the user wants. Yes, the user was able to recover some full backups, but they are very old.
So what I have is: old good full backup in .BAK file, corrupted up to date .MDF file and it's .LDF file, that is in unknown state since I don't know how to check it.
This database could only be attached by doing the 'workaround' of restoring it from the old full backup .BAK file, setting it OFFLINE, replacing the sucessfully .MDF file with the corrupted one. However, the database always get stuck at EMERGENCY or SUSPECT mode, and cannot proceed further from that. I can't even use CHECKDB since any ALTER DATABASE command will result in the described error.
I already tried to export the DATA from it by using both bcp command and manually from SSMS Task, but it also failed. I also used some free trial versions of renowned .MDF repair softwares, and it's indeed was able to capture good data, however the user is still skeptical on using these.
I also have tried to follow the instructions described here , but with no success since I can't get the database to go single-user, only EMERGENCY MODE. I also tried to start the server via SQLcmd and proceed from that, but it also failed.
Is there anything left that can be done? Can I fix the current state of those failed pages via HEX, even if they be gone afterwards? Any other source of export I could try? Did I tried everything in order to restore succesfuly?
**UPDATE:**
Since I had no option left, I did a thing that **is widely not recommended**: I restored the good backup in order to create a good .MDF file. I opened the good .MDF file with HEX editor and copied the faulty page adresses, then writed it to the bad .MDF file. It was my day, since by **incredible luck**, I was able to put the database ONLINE and execute alter commands, that even with some errors, placed the database in a position where I could execute checkdb and after that, I could execute some queries in key tables, retrieving some important data!!! The data, offcourse, had many holes on it, and was really painful to select the good sectors. This bad experience turned into a good one. I will not post that as a solution, as this is a really messy one. Thanks everyone who helped me here and in stackoverflow.
Asked by joaocarlosib
(23 rep)
Sep 4, 2024, 02:35 PM
Last activity: Sep 5, 2024, 01:15 PM
Last activity: Sep 5, 2024, 01:15 PM