Summary: The blog provides an exclusive information on SQL Server DBCC CHECKDB repair_allow_data_loss clause and how it results in loss of database contents when executed in the Emergency mode. It also suggests an alternative solution that replaces database console commands without data loss.
DBCC CHECKDB is executed on SQL Server database to check the physical and logical integrity of the integral components of the database. These objects include tables, pages, index relationship, values, and triggers. The procedure involves various structure checks. The database console commands are the initial recovery tactic when something unfavorable takes place.
In case, any of the stated checks fails while executing DBCC, the application will report an error message. There are many issues that call for the need to run SQL Server DBCC CHECKDB on database components. Such issues can be indicated with file system corruption, driver issues, hardware malfunctioning, problems with the SQL Server engine, and corrupt or damaged pages in memory. See common SQL database corruption errors and their solutions.
Let’s first understand what DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS fixes:
DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS tries to repair all the errors being reported by the application. However, the integrity of database objects is not promised as some data may be compromised or found to be missing once the command is executed. There are some warning messages associated with REPAIR_ALLOW_DATA_LOSS. These warnings include:
- Although REPAIR_ALLOW_DATA_LOSS is highly supported, it is not always considered as the best option for bringing the database to a consistent state. If the command is successful, it will result in loss of data. Executing this syntax may result in a considerable amount of data loss.
- The primary method for recovering the database from errors being reported by database console commands is to restore the database from last known good backup as recommended by Microsoft. This emergency option is recommended only in case it is impossible to restore from backup.
- It is recommended to create physical replicas of the database files before executing repair operation using REPAIR_ALLOW_DATA_LOSS. The files that need to be copied are MDF (master database file), NDF (secondary database files) and LDF (transaction logs) as well as file stream folders, full-text catalogs, memory optimized data,
- Always remember to change the state of the database to Emergency mode before initiating the repair process. It is suggested to retrieve all possible data from critical database tables or other objects and save them at an offsite location.
Read this: EMERGENCY-mode repair: the very, very last resort by Paul Randal
What Happens to the Database when REPAIR ALLOW DATA LOSS is executed?
When a database is in an Emergency state, and SQL Server DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS is executed, the following actions will take place:
- The given clause recovers SQL Server database by using log-based recovery strategies.
- If SQL Server database recovery is unsuccessful due to transaction log corruption, the log is rebuilt. The sudden rebuilding procedure could lead to transactional inconsistencies.
- Database pages that are marked inaccessible due to I/O checksum errors are used by DBCC CHECKDB pretending as if the errors have not occurred. This entire procedure accelerates the prospects of database recovery to a remarkable extent.
You can read this refrence here: DBCC Checkdb(Transact-SQL)
Are There Any Alternates of Repair_Allow_Data_Loss?
If DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS not working then Yes. As correcting errors by using REPAIR_ALLOW_DATA_LOSS clause involves loss of data, it is recommended to keep it at the end of the list in repair operation. To prevent data loss, an alternate solution can be considered. Stellar Repair for MS SQL would serve the purpose as it easily repairs corrupt SQL database from all types of corruption.
The damage that takes place due to application crash, virus attacks, sudden system shutdown, hardware failure, Operating System malfunction or other similar reasons can be handled with the help of this professional SQL database recovery tool. One major benefit that the tool offers is that it preserves the integrity of MDF and NDF file while repairing the SQL database and this makes it a potential alternative to DBCC CHECKDB repair_allow _data_loss clause.
Benefits of Stellar Repair for MS SQL
The software is integrated with an advanced level non-destructive algorithm that results in preserving the integral components of SQL Server database. Other benefits that the software offers while repairing database are as follows:
- The tool recovers database tables, index, triggers, rules, defaults, keys, and other objects
- Apart from recovering corrupt items, it also recovers deleted records from the SQL tables
- It searches and recovers particular database items which make the recovery procedure faster
- Saves repaired database in 4 different formats :MSSQL (.MDF), HTML, CSV, XLS
- It supports SQL Server database 2019, 2017, 2016, 2014, 2012, 2008 and lower versions
In the event of failure, both MDF and NDF files are affected. To efficiently repair SQL Database, it is important to repair all the components of the master database file as well as secondary database files.
If any SQL Server disruption occurs, the repair process is paused and automatically resumed when a connection to Server is re-established. This helps run the repair process in a smooth manner, whereby no data is lost during the process.
How Stellar Repair for MS SQL Works?
To get the best out of the recovery tool, it is important to understand its functionality. The corrupt database is repaired in the below-mentioned steps:
- Download and launch Stellar Repair for MS SQL
- Select the database that needs to be repaired by clicking on the Browse If you do not know the exact location of the database, click on the Search button to select the MDF file from a specific drive
- Once the database is selected, click on the Repair button to start the repairing procedure
- The tool will display all the objects of the database in the left panel. You can click on an object to view its contents in the right pane of the tool
- Next, the tool displays message denoting ‘Selected MS SQL Database repaired successfully’. Click on OK button
- Next, the tool will ask for the file format. Select the desired storage option and then provide destination location by clicking on the Browse button
- If you want to restore the repaired database to the ‘New Database’ or ‘Live Database’, you need to provide Server credentials. Once complete, click on the Save button to proceed further.
- Lastly, click on the OK button when ‘File saved at the desired path’ dialog box appears.
Trusted by Microsoft MVP (MVP recommendations and Best Practices)
With this, the corrupt database is repaired successfully and stored at the desired location with all the integral attributes intact. Therefore, the third-party application is considered more efficient as it does not involve the risk of data loss, unlike repair_allow_data_loss.