What is Repair_allow_data_loss?

by
What is Repair_allow_data_loss

Repair_allow_data_loss is a parameter used in SQL Server database recovery operations. It allows a database to be recovered even if some data loss occurs during the recovery process.

When is Repair_allow_data_loss used?

Repair_allow_data_loss is used when a database is in a suspect or damaged state and cannot be recovered using normal means. Some scenarios where Repair_allow_data_loss may need to be used include:

  • Transaction log file corruption – The transaction log files contain a record of all transactions and database modifications. If these files become corrupted, the database cannot be recovered normally.
  • Inaccessible data files – If a database’s data files are corrupted or deleted, a normal recovery is not possible.
  • Hardware failures – Failures like disk errors can prevent access to parts of a database, making a typical recovery impossible.
  • Server crashes – An abrupt SQL Server shutdown can leave a database in an inconsistent state that requires Repair_allow_data_loss.

In each of these cases, using the Repair_allow_data_loss parameter enables a forced recovery of the database even though some data may be lost in the process.

How does Repair_allow_data_loss work?

When Repair_allow_data_loss is specified, the SQL Server recovery process works differently than normal. It essentially tells the database engine to recover and reconnect as much data as possible, without regard for consistency and integrity checks that would normally prevent data loss.

Some key things that happen when Repair_allow_data_loss is used:

  • No rollback is performed – Uncommitted transactions are not rolled back and may result in data inconsistencies.
  • No consistency checks – Checks that validate things like foreign keys and constraints are skipped.
  • Pages restored as is – Data pages are restored without verifying checksums or consistency between pages.
  • Orphaned pages possible – Pages pointing to data that cannot be accessed are left orphaned.

By skipping these checks and validation steps, data can be left in an inconsistent or incorrect state. However, the database is recovered to an online status so that at least some of the data is available.

What data may be lost with Repair_allow_data_loss?

There are several types of data loss that can occur when using the Repair_allow_data_loss parameter:

  • Uncommitted transactions – Transactions that were in progress at the time of the database failure may be rolled back, resulting in data loss.
  • Orphaned rows – Rows that have relationships to data that couldn’t be recovered can become orphaned and may be dropped.
  • Pages with errors – Pages containing checksum errors or other corruption may be dropped entirely if they can’t be repaired.
  • Incorrect rows – Without consistency checks, rows may contain incorrect or out-of-sync data.

In most cases it’s difficult to predict precisely what data will be lost or left in an incorrect state. The amount and type will depend on the specifics of the failure and corruption.

When should Repair_allow_data_loss be avoided?

Because Repair_allow_data_loss can lead to permanent data loss and database inconsistencies, it should be used with extreme caution and only as a last resort. Some scenarios where you will typically want to avoid using this parameter include:

  • Production databases – Data loss could be catastrophic in live production databases.
  • Financial data – Incorrect or missing financial transactions could cause major problems.
  • Legal data – Losing audit trails or other compliance-related data poses regulatory risks.
  • High value data – Customer details, pricing data, statistics – any loss could significantly impact business.

Unless the database is a non-critical test or development system, Repair_allow_data_loss is not recommended in most circumstances.

How to use Repair_allow_data_loss

If it is absolutely necessary to use the Repair_allow_data_loss parameter, this is the basic process to follow:

  1. Take the database offline and ideally stop any other activity on the server.
  2. Backup the damaged database if at all possible.
  3. Determine the cause of the failure and repair any underlying issues if possible.
  4. Run DBCC CHECKDB on the database and review the output.
  5. If CHECKDB cannot run or fails, execute a repair statement like: 
    RESTORE DATABASE DatabaseName  
WITH RECOVERY  
, REPAIR_ALLOW_DATA_LOSS
  6. Bring the database online and evaluate data for integrity.
  7. Perform any additional repairs on orphaned or damaged objects.

This process helps reduce additional data loss beyond the initial recovery operation.

Example using T-SQL

Here is an example T-SQL statement performing a restore with Repair_allow_data_loss:

RESTORE DATABASE SampleDB   
FROM DISK = 'C:\SampleDB.bak' 
WITH RECOVERY,  
REPAIR_ALLOW_DATA_LOSS

Best practices when using Repair_allow_data_loss

If Repair_allow_data_loss is necessary, following best practices can help reduce potential data loss:

  • Take a full backup of the damaged database first
  • Test the recovery process on a copy of the database if possible
  • Document all recovery steps for auditing
  • Restore to a point-in-time prior to the failure if able
  • Export any accessible data before performing the restore
  • Have business users ready to review critical data when online
  • Be prepared to repeat the repair process if needed

Careful planning, validation, and user involvement can help mitigate the risks of this method.

When is data loss acceptable?

There are a few rare cases where Repair_allow_data_loss and some potential data loss may be deemed acceptable:

  • Non-production environments – Repair options can be tested more aggressively on test and dev databases.
  • Non-critical data – Some data sets are more important than others.
  • Limited options – If there are literally no other recovery options remaining.
  • Cost/benefit tradeoff – The business value gained by restoring the database may exceed the value of some lost data.

However, these situations are the exception. For most production databases, especially those containing critical data, Repair_allow_data_loss should only ever be a desperation measure when all else fails.

Alternatives to Repair_allow_data_loss

Because of the risks, DBAs should always look for alternative recovery methods before resorting to Repair_allow_data_loss. Some options to consider:

  • Restore from backup – Look for valid, non-corrupted backup files that can be used to restore to an earlier point.
  • Emergency repair – Run manual DBCC CHECKDB, DBCC PAGE, and other repair operations to fix issues.
  • Salvage parts – Restore or export any recoverable parts of the database.
  • Third party tools – Specialty products may be able to repair certain database problems.
  • Point-in-time restore – Restore to a time just before the failure occurred.

Taking the time to thoroughly explore and exhaust all these options is preferable before turning to Repair_allow_data_loss as a final resort.

Impact of losing data

The impact of data loss will depend entirely on the database, the applications, and the business:

  • Lost transactions can result in incorrect account balances, order status, inventory levels, etc.
  • Missing audit and activity log data may pose compliance risks.
  • Applications may malfunction or crash if reliant on lost data.
  • Analysis and reporting will be incorrect if the underlying data is wrong.
  • Customer experience may suffer if records are incorrect or missing.
  • Legal problems could occur if certain required datasets are lost.

In some cases the downstream impact may not be known until business users resume activity. Rigorous validation is crucial after any Repair_allow_data_loss operation.

Conclusion

Repair_allow_data_loss is a powerful but dangerous recovery method. It can allow a database to be recovered when all else fails, but at the potential cost of permanent data loss and inconsistencies.

To summarize the key points:

  • Use Repair_allow_data_loss only as an absolute last resort when no other options remain.
  • Avoid it whenever possible on production systems and databases.
  • Take every precaution to minimize data loss if using it.
  • Thoroughly validate and audit the database after performing the recovery.
  • Have business users check critical processes and data closely.
  • Understand there are often hidden costs and impacts from losing transactions or records.

With proper care, testing, and use as a last ditch effort, Repair_allow_data_loss can allow a crucial database to be recovered. But all alternatives should be exhausted before accepting the risks and repercussions.