We were recently blessed with data corruption on a system table within a user database. The corruption was 5 days old when we noticed it (don’t ask why) and each process that triggered a checksum failed. Result: no backups more recent that 1 week; page level restore was no option. An additional level of complexity was that the DB had CDC enabled. This is what we did:
- Restore DB of 1 week old using the keep_CDC option as a _new DB
- Create both capture and cleanup cdc jobs on the _new DB.
- Alter the retention option of the cleanup job to 1 minute.
- Delete all content from user tables
- Copy over all content from the corrupted DB
- Launch the CDC cleanup job until all _CT tables were empty.
- Alter the retention option of the cleanup job to 7200 minutes (the default).
- Rename corrupted DB to _OLD
- Rename the _new DB to the original name
- Run DBCC FREEPROCCACHE
This last step was needed for stored procedures that were cached and still refered to the corrupted DB.
Life can be fun.