Do I really need a backup when using REPAIR_ALLOW_DATA_LOSS?

Question

Reluctant DBA here.

Do I really need a backup when using REPAIR_ALLOW_DATA_LOSS? I am well aware MS recommends doing a backup. What's the worse that could happen if I run a DBCC CHECKTABLE using REPAIR_ALLOW_DATA_LOSS?

A bit of background.

A client wants to migrate the DB used by our software from on-prem to Azure. Currently, the DB size is roughly 1TB and he wants to delete a lot of data in order to get it below 500GB before migration.

He has no DBA. There are no backups whatsoever. Not enough space for backups.

I wrote a program that deletes data older than the threshold agreed upon, but while deleting data from one of the tables for a certain date, I got a Fatal Error 824. DBCC CHECKDB output:

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 914102297, index ID 1, partition ID 72057594051493888, alloc unit ID 72057594057195520 (type In-row data), page (1:3692983). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.

Msg 8928, Level 16, State 1, Line 1
Object ID 914102297, index ID 1, partition ID 72057594051493888, alloc unit ID 72057594057195520 (type In-row data): Page (1:3692983) could not be processed.  See other errors for details.

Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 914102297, index ID 1, partition ID 72057594051493888, alloc unit ID 72057594057195520 (type In-row data). Page (1:3692983) was not seen in the scan although its parent (1:3693476) and previous (1:3692476) refer to it. Check any previous errors.

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 914102297, index ID 1, partition ID 72057594051493888, alloc unit ID 72057594057195520 (type In-row data). Page (1:3693015) is missing a reference from previous page (1:3692983). Possible chain linkage problem.

CHECKDB found 0 allocation errors and 4 consistency errors in table 'schema.FancyTableName' (object ID 914102297).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'FancyDatabaseName'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FancyDatabaseName).

The table in question contains non-critical data. The client is OK with losing data for that day (it's older than the threshold). No table depends on it. The table itself has a foreign key dependency on another table.

So, what's the worse that could happen when running DBCC CHECKTABLE('schema.FancyTableName',REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK? I need to run it with TABLOCK since there's not enough space for the internal database snapshot.

Thank you!

Answer 1

Table error: Object ID 914102297, index ID 1,

The index ID =1 shows it has clustered index so corruption here will most probably lead to some data loss. Hopefully only to this table. But it is hard to tell actually what all pages will be removed to remove corruption. So it is a risky task

Do I really need a backup when using REPAIR_ALLOW_DATA_LOSS?

Its advisable and safe ( if the backup runs depending on level of corruption) but since you are ok with data loss and that data is non critical and you really dont have space you may proceed without backup. But here is other option.

1. Create a empty database
2. Script out all objects except that which is corrupt. Use database script wizard
3. Move data from old DB to new DB using import export wizard present in SSMS. This is assuming you have free space. I am not aware how far has your delete operation succeeded on other tables. But this is safest way. The drawback here is it will take time and patience. You can also only move data you need and leave the old ones in old DB.
4. Once above is complete run full dbcc checkdb with no_infomsgs, all error_msgs in new db. make sure it does not reports any error.
5. Delete the old DB.
6. Find out why corruption occurred. Most probably faulty hardware.
7. Get a weekly (atleast) checkdb job configured.

So, what's the worse that could happen when running DBCC CHECKTABLE('schema.FancyTableName',REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK?

The data can be deleted more than what you are expecting.

If you want it to be easy task. I would suggest to add space. But again if you are willing to take risk of data loss you are more sensible than me in that case since you know your data.