5

The MSDN's article about the command "DBCC CHECKDB" explains three ways to perform a database repair in the syntax section:

- REPAIR_ALLOW_DATA_LOSS
- REPAIR_FAST
- REPAIR_REBUILD

But I found the following statement when I was looking for how to repair a suspect (I put in this mode doing these steps) database and I can't understand which one of the three modes is it:

DBCC CHECKDB (databaseName, repair)

I executed the statement and it works fine. I'm confused because there isn't any reference to the "repair" parameter alone without finish the word with "_allow_data_loss", "_fast" or "_rebuild".

If I try to run "DBCC CHECKDB (databaseName, repair_rebuild)" on my database in emergency status, I receive a error message saying I can't execute that level of repair while the database is in emergency status. So, I discard that "repair" alone is "repair_rebuild".

That statement is the third line in the script that I found:

  1. ALTER DATABASE databaseName SET EMERGENCY;
  2. ALTER DATABASE databaseName SET SINGLE_USER;
  3. DBCC CHECKDB (databaseName, repair) ALL_ERRORMSGS
  4. ALTER DATABASE databaseName SET MULTI_USER;
  5. ALTER DATABASE databaseName SET ONLINE;

If I read the output of the third statement execution I see information about the process but nothing related on the level used in the repair.

Thanks in advance,

  • What script did you find this in? – Max Vernon Dec 17 '15 at 15:45
  • @MaxVernon My coworkers use it when they need to repair a suspect customer's database. Now, I have a worktask to automate the process of repairing databases and I can't understand if "DBCC CHECKDB (databaseName, repair) ALL_ERRORMSGS" allow data lose or not. And I need to determine if execute the checkdb with the "repair" argument can lose data because is a functional requirement of my worktask avoid data lose. Thanks for your help and sorry for my english. – agdiaz Dec 17 '15 at 17:45
  • 1
    I would suggest that REPAIR and REPAIR_ALLOW_DATA_LOSS should be treated the same; that is they both allow data loss. – Max Vernon Dec 17 '15 at 18:50
3

Running this on a sufficiently corrupted database results in the following error message:

Msg 7909, Level 20, State 1, Line 1

The emergency-mode repair failed.You must restore from backup.

This leads me to estimate that REPAIR is simply the short form of REPAIR_ALLOW_DATA_LOSS. In the very least, I would treat it as the same when it comes to expectations around recoverability of the database in question. In other words, this should be considered the operation of last resort, only to be tried when all else fails. Make sure you make a physical copy of the database before running this process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.