REPAIR_ALLOW_DATA_LOSS is the repair level, which is recommended by DBCC CHECKDB when the database is found in a corrupted state.SQL users and even administrators at times get confused about the real functioning of this command. It is to be noted that it contains the term Repair, which signifies that the database in concern will be repaired whilst deleting the corrupted data residing in the database.Repair means that the data will not be simply fixed but will be brought in a consistent state, even if it requires deleting some data.

DBCC CHECKDB is accompanied by the REAIR_ALLOW_DATA_LOSSbecause issues apart from minor issues of non-clustered index, requires deletion of some data for the purpose of its repairing.If a corrupt record is found on the data page, the command will end up in deleting the entire page inclusive of all other records for the sake of fixing the corruption issue. Therefore, the developers have carefully added the term “ALLOW_DATA_LOSS” after the term REPAIR in order to make it clear to the users that the repair process will be done while allowing some data loss.

Repair & Data Deletion

Even experienced SQL administrators at times, get confused about what REPAIR_ALLOW_DATA_LOSS actually does. It is to be noted that the sole purpose of repair is not to save the user data. On the contrary, repair is analogous to the fact that some data is bound to get deleted in the whole process.

The repair command is used to bring the database in a consistent state structurally in a correct way and that too without any delay. Therefore, the repair process is engineered in a way that it will work efficiently in every circumstance in a fast and reliable manner.It simply deletes the data that is corrupted along with the other data items to which it's linked, no matter if it’s a record or a page.The repair process only deals with the physical structure of the database and has no concern about the high-level logical structure.

The end-result may be a little different owing to the deletion that is done. However, the consistency of the database structure will be the same as it was before corruption.

When DBCC CHECKDB command is run, a recommendation stating the minimum repair option is provided to the user. The message may look like this:

CHECKDB found 0 allocation errors and 10 consistency errors in database 'projectdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

The recommendation provided is the minimum level of repairing that is required to fix the database. Moreover, not all the errors that are reported require this repair level.

Conclusion

The best solution for fixing a SQL Server database without fearing loss of data is the restoration of data from a healthy and latest backup. This will enable the users to restore the entire data without doing any compromises with the integrity of the database or if you don't have a healthy backup then you can try alternate solution like third-party tool and bring your database back.