Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a server that is throwing an error message when I try to backup a database. The error is:

10 percent processed.
20 percent processed.
Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file "R:\data\MyDB.mdf:" 
         23(Data error (cyclic redundancy check).).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I thought it was a database corruption, so I executed:

DBCC CHECKDB([MyDB]) WITH NO_INFOMSGS;

and no errors were found.

What could be happening? What are your suggestions regarding this datafile?

share|improve this question
    
Can you query data with out issue/error? Are you using SQL Server native backup? Is your backup location local on the server or a network share? –  Shawn Melton Oct 10 '12 at 16:28
    
The backup is SQL Server native. I am backing up to a local drive and there is plenty of space. The database is about 20 GB I don't know which table is corrupt but the tables I have queried returned the correct results. –  mvillegascuellar Oct 10 '12 at 16:56
    
Did you try CHKDSK? Could be physical disk problem. And try "DBCC CHECKDB (DBName)" (without "NO_INFOMSGS"). –  Justicator Oct 10 '12 at 16:57
1  
1  
@JNK this is not a duplicate. Question 21960 was about WRITING the backup file (free space problem), This one is about READING the .mdf file, possible physical corruption of hard drive. –  Justicator Oct 11 '12 at 15:16
show 1 more comment

1 Answer

If you are experiencing corruption, the first thing I would do is make sure you have a valid backup from a previous run on standby. Restore that somewhere (if possible) so you have access to the data.

You will need to run a DBCC CHECKDB to see what exactly is going on. If DBCC CHECKDB comes back with no problems, delete your backup file and try re-creating it again from scratch (it may be a corrupted backup file).

If DBCC CHECKDB shows errors, you can try running a repair_fast or a repair_rebuild on the database, but more than likely you will need to run a repair with the repair_allow_data_loss option. As the option says, you may lose data. If the problem lies with an index, just drop / recreate the index. If the problem lies with a data page, then you will need to correct it with the repair_allow_data_loss option and deal with the loss.

After you correct the error, ensure the database page_verify option is set to CHECKSUM.

ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM

To find the lost data, you could try to take a new full backup and try using a data comparison tool against the old backup and the new backup to identify records that may have been lost. This will be difficult and time consuming. There may be a better way using the pages displayed in DBCC CHECKDB when the repair is run but someone else would have to fill in that part as I'm not too familiar with this.

share|improve this answer
add comment

protected by Jack Douglas Oct 23 '12 at 12:22

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

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.