6

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?

  • 2
    Stopping the service was a really bad idea. You're quite lucky the database came up when you restarted. – Aaron Bertrand Oct 23 '14 at 11:54
  • Never attach/detach or stop SQL server services for database which is corrupt you might loose all possibility to recover – Shanky Oct 23 '14 at 18:05
2

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)

2

There is one more answer which could be helpful in this case so adding it.

Did you tried taking backup of database with continue_after_error Query would be something like

BACKUP DATABASE db_name to disk='location'
with continue_after_error

If it completes successfully its great now restore this backup on DIFFERENT server again using option continue_after_error(read official documentation) if all goes well and you are restore of database completes successfully run

dbcc checkdb ('db_name', repair_allow_data_loss )

and see for possible data loss. This would give you great idea about how much data loss could happen. And then you can plan accordingly about informing client about how much data loss occur.

A very important point missed is did you tried finding reason why database went corrupt this should be on top priority because recovering from data loss is just half job done finding the cause and mitigating it will be complete solution. You should refer to SQL Server errorlog and windows eventviewer for more information/clue as to what could have caused possible corruption. Looking at error message it could be because of underlying disk corruption.

PS: In this scenario I wont think Third party tool would help more than apart from what is mentioned in both answers

  • thank you for your help. I have already tried "continue_after_error" but it didn't help. It appears that there is something wrong with the disk/file system which caused the datafile corruption. – suneeth Oct 24 '14 at 9:04
  • Well as I said it might it's not guaranteed what about the other solution mentioned by Kenneth. BTW u also posted same question on sql team forum – Shanky Oct 24 '14 at 10:57
  • thank you for your help. I am able to generate scripts only for the schema not for the data. when I try to generate the script for the "Data" it gives me timeout error. Marian has suggested to use the BCP tool for data exporting, but it appears that BCP tool cannot be used to export the entire database, it needs to be done table by table. Unfortunately our database is massive and not sure exporting table by table is an option – suneeth Oct 24 '14 at 13:18
  • @user2996309 BCP needs to be done table by table, indeed, but that shouldn't be an issue, there are articles like this or this or this or this that can help. – Marian Oct 24 '14 at 23:04
2

Not a complete answer by any means, but couldn't fit in a comment:

1st: Don't stop the service, don't detach the db, don't take it offline.

2nd: Try to bcp the hell out of it:

  • follow @Kenneth's advice for the schema, and for the data too, if it's only a small db;
  • if it's a big one, don't script the data, create files by exporting to them with bcp (faster and easier) but NOT on the same drive as the data file is on (in case that's the problem)

3rd: Before running DBCC with any of the repair options, please take into consideration that:

  • they're done in single user mode, so the database is offline during the operation (so plan the outage);
  • with a repair option CHECKDB is using a single thread (so it's slower than the check without a repair);
  • you can wrap the DBCC operation inside a transaction so in case you don't like how it fixed the data, you can rollback;
  • Paul - dbcc checkdb's daddy - reccomends that we try to run the smallest possible first - CHECKALLOC, CHECKTABLE, and CHECKDB as a last resort (longest time to run).
    • for CHECKTABLE I would get a list of objectIds from your initial error and try to fix those tables first;

Unfortunately, I can't help not showing you the MSDN warning:

Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

protected by Mark Storey-Smith Jun 16 '15 at 9:38

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.