2

I have been asked by my interviewer "How do you troubleshoot corrupted data in SQL in SQL Server 2012?"

Naseer Aryobee
  • 115
  • 1
  • 4

1 Answers1

3

Well, right off I would verify with the person asking the question that they are referring to corrupted data and not database corruption, there is a difference. I would also determine who was interviewing me and base the answer on that (e.g. non-DBA would not care about the details, where a DBA Manager or something could require me to provide more details).

For somewhere in the middle...

Corrupt Data

With corrupt data entering the database it would be based on how quickly it was caught and likely how wide spread it is (isolated to a table, or effects multiple tables). If a single table was involved it could be as easily as restoring a copy of the database and replace the data in the table. If the database is large this may not be so easily, or if multiple tables are involved, require recreating the data. The focus would be in designing of the application to put in controls that prevent this from happening to begin with because of the pain it can cause, and possibly be unrecoverable.

Database Corruption

Database corruption would mean the database as a whole or underlying structure of the database is damaged. I would start out taking a full backup of that database in its current state before starting anything. I would start out reviewing the previous DBCC CHECKDB output since I would have had that information readily available for systems I manage. Reviewing that output would indicate what level of severity I am dealt.

I would want to determine and ensure the underlying disk the database files reside on are in good condition and may not have been the cause of the issue.

I would then start re-reading post from folks like Paul Randal on corruption recovery to ensure I fully understood the liability of my actions, before doing anything. In a last ditch effort and if the tables that are found having the problem have data that can be recreated easily, I would issue the DBCC CHECKDB WITH ALLOW_DATA_LOSS if it was in a critical timeframe. Outside of that it is time to prepare for the restore process.

I would also go into small rant that alerting and ensuring backups are good, and meet the business needs, would ensure the company is ready to easily handle this type of issue. If my backups are current and good, I would just start the restore process to another database, while I was trying to recover the corruption. This would allow me to have a "backup" plan if the recovery steps above did not work. It is all really based on the criticality of the system.