DBCC CheckTable repair_allow_data_loss

Question

Post reply

DBCC CheckTable repair_allow_data_loss

MickyD

SSCarpal Tunnel

Points: 4601
More actions
February 4, 2014 at 2:46 am

#283125

Hi everyone.
After some advice.
I have a database reporting 35 consistency errors in a table.
I want to test the repair process before running on prod box.
My plan to test is as follows.
Copy to another box
Restoring database
See if DBCC error is there
Then try to repair allow_data_loss to judge what is lost.
Is that a sensible approach , any others ways to tackle this issue ?
many thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do you have a clean backup (from before the corruption) and an unbroken chain of log backups?

Since it's a reporting DB, can it be recreated from another source?

Is that the only corruption in the database?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

If the inconsistencies are internal to the database and not an external storage problem, your approach might (huge, huge, word) be a good approach. Running repair_allow_data_loss is a complete gamble along the lines of placing a single bet on the roulette wheel. You may win big, but you're most likely going to lose. Follow Gail's advice. Get a good backup and a set of logs and restore to a point prior to the corruption.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 3

Thanks everyone.

Yes we do have backups, I will get one of them lined up.

thanks for advice.

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 4

Another thought....

I run my DBCC checks weekly , on Sunday evenings.

I run backups daily , and they are retained for 2 days on box then moved to tape.

So I can tell the corruption occurred between 2 dates.

Is there any way I can detect a date when corruption occurred , in order to get the latest non corrupted database backup back from the tape vendors ?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Not that I'm aware of. Generally, for full backups, I do one of two things, run DBCC before them, or use them to run DBCC (and then run a physical only DBCC on the main server) after restoring them to another location. Divorcing completely the consistency check from the backup means you're going to have to experiment to find the right spot.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

MickyD (2/4/2014)
Is there any way I can detect a date when corruption occurred , in order to get the latest non corrupted database backup back from the tape vendors ?

It occurred sometime after the last successful checkDB and sometime before the first occurrence of a corruption-related error or failed checkDB. A misbehaving IO subsystem (which is what most corruption is caused by) is not going to be polite enough to make an entry in a log when it corrupts the DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 7

MickyD

SSCarpal Tunnel

Points: 4601

February 4, 2014 at 7:51 am

#1685871

thanks for updates.

All make sense.

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 8

As Grant pointed out you really need to perform all of the actions together to have an idea of when the corruption occurred (what day). I always take my backups -> restore backups (different machine) -> and run CHECKDB every day (automated of course). I would suggestion something like that.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 9

Keith Tate (2/4/2014)
As Grant pointed out you really need to perform all of the actions together to have an idea of when the corruption occurred (what day). I always take my backups -> restore backups (different machine) -> and run CHECKDB every day (automated of course). I would suggestion something like that.

Keith sounds like a very sensible plan. Many thanks.

Any chance you could share some of the steps , scripts you use to do this ?

Automation has to be way forward on this for me.

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 10

I am currently using Ola Hallengren's[/url] backup scripts for my backups and a custom SSIS package that I created to perform my restores and DBCC CHECKDB. The package is nothing fancy and I am planning on creating some PoSH scripts to replace the SSIS package. Once I have finished them I will put them on my site.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 11

Keith Tate (2/4/2014)
I am currently using Ola Hallengren's[/url] backup scripts for my backups and a custom SSIS package that I created to perform my restores and DBCC CHECKDB. The package is nothing fancy and I am planning on creating some PoSH scripts to replace the SSIS package. Once I have finished them I will put them on my site.

Excellent thanks Keith.