Do Not Run REPAIR_ALLOW_DATA_LOSS

So often I get the call from someone needing help with repairing a corrupt database, and then they tell me that they ran CHECKDB with REPAIR_ALLOW_DATA_LOSS and at that point I realize it is going to be more difficult to get all their data back and repaired.

When working with CheckDB and SQL Server, whether you are on SQL Server 2019, or on an older SQL Server 2005 system, you should not run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS until you understand what is going to be lost.

What REPAIR_ALLOW_DATA_LOSS allows checkDB to do is to simply throw out any data that is on a corrupt page. A page in SQL Server is an 8K block of data in the database, that may be a page that contains rows, or it may be a page that contains index data, or even a page that has larger VARCHAR and VARBINARY data on it. When you run DBCC CHECKDB or DBCC CHECKTABLE with the REPAIR_ALLOW_DATA_LOSS option it simply tells SQL Server to scan through the database until it finds any page (8k block of data) with corruption on it, then to just throw that 8k page out.

If you value the data in your database then you should see how bad this is. Throwing those corrupt pages out may mean deleting many rows out of a table.

The other thing that using REPAIR_ALLOW_DATA_LOSS means is that once you have dumped those corrupt pages, there is no option to restore them. With a proper corruption repair we can copy that data that my be deleted, save it off, then put it back in after the corruption has been repaired.

You may see the following message in the output from DBCC CheckDB which can be a bit misleading, implying that it is going to repair your data, were instead it should say it is going to throw away your data.When it comes to database recovery, repair_allow_data_loss is about the worst thing you can do.

repair_allow_data_loss is the minimum repair level

At Stedman Solutions, LLC we regularly do corruption repair with the focus on recovering the data instead of just using REPAIR_ALLOW_DATA_LOSS to throw it away. If you value you data please reach out to us before throwing your data away with REPAIR_ALLOW_DATA_LOSS. We can help!

Even if you have just run with REPAIR_ALLOW_DATA_LOSS, get in touch with us and we can see what we can do to help.

Related Links:

• REPAIR_ALLOW_DATA_LOSS articles
• Database Corruption Overview for Beginners
• Stedman Solutions, LLC database corruption repair services

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!