It’s quite common for a company that experiences a corruption-causing disaster, but has no valid backups to restore from and no ability to fail over to a redundant secondary, to just run repair and then immediately start running in production again.
<Imagine there’s a clever GIF here combining OH NO! UH OH! OMG! SMH! NOOO!>
Over the years I’ve taught countless classes and conference sessions that talk about corruption and how it happens, how to run consistency checks, using DBCC CHECKDB, and running repair. When I’m talking about repair, I explain that if you have to run REPAIR_ALLOW_DATA_LOSS and it actually deletes data records, you need to reinitialize any affected replication topologies, and validate any affected constraints. This is all documented in Books Online, but that doesn’t mean that people read it and know that – lol!
Check It Out!
What *isn’t* in Books Online, and I make sure the attendees realize, is that repair is all about making the database structurally consistent – it doesn’t know anything about the data relationships between tables (either protected by constraints or just inherent in the schema design). This means that after a data-losing repair, the data relationships in the database may well be broken.
I then always ask the audience: “How many of you have an application data-consistency checker that you can run to validate all the business rules and relationships that the application depends on? In fact, how many of you even have a way to test that the application is working correctly after running a repair or deploying new code?”
Every time I ask, I might (rarely) get one or two hands go up, out of a class of 30 or a session of 50 or more. I’ve never had more than two, and usually I don’t get any.
It’s just not something people think about. They assume that if repair runs correctly then they can carry on and everything will work. No.
Call to action:
You really should have some way to validate that your application is running on correct data. Otherwise, in the best case, it will fail, but in the worst case, it will continue running erroneously – maybe with wrong results that affect your business.
This basically means codifying the required relationships in constraints and/or some code that verifies the required relationships (if they can’t be expressed as relational constraints) are correct.
If you have a third-party application, it might be hard or impossible to persuade the vendor to provide such a tool, especially if they specifically don’t support running repair on their database.
Alternative: have a bullet=;proof baclup-and-restore strategy and/or failover solution.
Bottom line: Your business depends on the applications running in the data tier, and you need to make sure, as much as you can, that they’re running on correct data.