What is recommended approach for fixing corruption related errors?
When there are errors of code 823 in the log or dbcc has detected corruption, then should the 1st choice to solve this be:
- dbcc checkdb with no data loss
- or restore from full backup+tlog backups?
DBCC checkdb does not fix corruption, it highlights the corruption. DBCC checkdb with
repair_rebuildis there but in most cases it would not work.
repair_allow_data_lossis there but again it would delete data, remove constraints and leave your database vulnerable post execution, again no guarantee that it would always work. The best option in most cases is to restore from good backup.
Please read article from Paul Randal SQLskills.com