The question:
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?
The Solutions:
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.
Method 1
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