Which type of repair level is “DBCC CHECKDB (databasename, repair)”?

Question

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,

Answer 1

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.