Sql-server – Corrupted mdf file causing the backup job to fail

sql serversql-server-2008

We have ran into a problem with one of our SQL server 2008 databases. It appears that something is corrupted in its datafile and this has caused our daily DB backup jobs to fail. The last successful backup copy taken was a month ago 🙁

Luckily the database is up and running and the application is able to connect to it. The only problem is it doesn't allow the db to be backed up anymore.

  • When I try to take a backup it gives me "Read on e:\xxx\xyz.mdf file failed. reason 15105" error.

  • I have stopped SQL server service and tried to manually copy the mdf and ldf files but the system doesn't allow us to copy the mdf file. It gives a "Cannot read from the disk" error

I have ran the DBCC CHECKDB command to check the errors and below are the results

1) Unable to read and latch page (1:43515) with latch type SH.
23(failed to retrieve text for this error. Reason: 15105) failed.

2) Object ID 567673070, index ID 1, partition ID 72057595461697536,
alloc unit ID 72057595536211968 (type In-row data): Errors found in
off-row data with ID 1788936192 owned by data record identified by RID
= (1:44202:25)

I was thinking of running the DBCC command with REPAIR_ALLOW_DATA_LOSS but I am really scared of losing the data. If something terribly goes wrong we don't even have a recent backup copy to restore.
It is a huge risk for us to have a production DB without having the ability to back it up.

  • What would be the best way to repair the corrupted datafile without data being lost? do you recommend any third party tools?

  • Is there any other way that I can backup/take a copy of this database before starting to repair?

Best Answer

Given that you can connect to the database you may actually be in pretty good shape. There are a number of methods you can use that might work but here is one.

  1. Create a new database preferably on another disk because the possibility is that your disk is corrupt.

  2. Use generate scripts to generate a script for your existing database. Include all database objects and permissions.

enter image description here

  1. Run the script on your new database to create all of the structures you need.

  2. Either manually or through a tool (Import/Export Wizard for example) copy the data from each of the tables on your corrupt database into the new database. Unfortunately this is the step most likely to have problems. If you do have a problem it will hopefully be with only a few records and you can use the Import/Export Wizard to generate an SSIS package and change your error setting for that particular table to ignore failures.

Either way once you are done take a backup. (Of course)

Related Question