I am learning about dbcc checkdb and that it detects corruption.
Suppose checkdb results show corruption in db.
What is the way to fix the corruption suppose I have the db backup?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
You don’t repair it using the backup. You restore from a prior backup which is clean. I.e., the basics are, after detecting the corruption:
- Perform the last log backup if possible, make sure that no-one is doing further modifications in the database.
- Restore from a clean full backup
- Restore from a subsequent clean diff backup, if you have such.
- Restore an unbroken chain of log backups, if you have such, including the very last one you took in step 1.
You hopefully now have a clean database. Of course there are a lot of aspects around this (doing root cause analysis, etc). Brent Ozar has written a great blog post on this that I highly recommend: https://www.brentozar.com/archive/2016/05/dbcc-checkdb-reports-corruption/
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0