Have you ever wondered that you are working on Microsoft SQL Server and database shows some abnormal file crash or error issue? And, when you execute the DBCC CHECKDB command, it reports corruption in your SQL Server database.
Definitely, this scenario is a nightmare and the saddest part, it is frequently faced by many SQL database administrators.
Now, the biggest question is ‘What to do when DBCC CHECKDB reports corruption in database?’
So, The long wait is finally over!
Here, we are going to tell you what step should be taken when the SQL database gets corrupted. Thus, one can secure its confidential and important database in any data disaster situation. But, before understanding the method, a user must know about DBCC CHECKDB and which type of corruption it is reported?
Without wasting any minute, Let’s begin!
Take a Quick Look on SQL Server DBCC CHECKDB
DBCC CHECKDB is a type of T-SQL command whose prime function is to monitor the logical as well as physical integrity of all object in a selected database. DBCC CHECKDB command is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, Azure SQL Database, and SQL Server 2019. Furthermore, it comprises three main commands in it for different purposes.
DBCC CHECKALLOC – Its function is to verify the organization of disk space allocation or page usage for a selected SQL database. Besides this, it also checks various internal structures to maintain the record of allocation of all pages in the database as well as relationship between them too.
DBCC CHECKTABLE – This Transact-SQL command is used to check the logical and physical consistency of the pages and structure that create table or system table indexes.
DBCC CHECKCATALOG – This command is used to perform various catalog consistency between system metadata tables. For the execution of this T-SQL command, the database must be in online mode.
The above-described command runs as a part of DBCC CHECKDB. If the user executes the DBCC CHECKDB command, then there is no requirement to use all these commands separately. In case, when the database DBCC check reports any error, just take the help of following solutions in order to save the important database table.
Methods Use to Repair Corrupted Database in SQL Server
In the following section, three different workarounds will be described to restore data objects from a damaged or corrupted SQL database. A user can go through all these methods and select that is appropriate.
Approach #1: Use REPAIR_ALLOW_DATA_LOSS Command
When the DBCC CHECKDB reports corruption error, then you can use Repair command on the specified database. It is devised to fix all the reported error. However, the biggest disadvantage of this method is it leads to data loss. Even, when the user uses this command to extract the database from last clean backup, it results in more data loss. In fact, Microsoft does not recommend this method suitable to recover data from the good backup. It is marked as an emergency option that can be used when the data cannot be recovered from the last backup file.
Approach #2: Restore Database from Backup
If the last good backup of database is available, then it is better to recover the database from it with the help of RESTORE DATABASE command. With the help of Restore statement, one can perform multiple operations.
- Retrieve only a part of SQL database.
- Recover specific pages of a database.
- Extract complete database from full backup.
- Capable to get back a transaction log onto a database.
- Provide an option to restore only desired file or filegroups.
In case, when the user does not have a last clean backup of the database, then the Restore command does not work.
Approach #3: Use Expert Solution SQL Database Recovery
The above two solutions have some consequences, so it is better to show your trust in a reliable solution because data loss cannot be ignored. That is why many SQL administrators uses MDF Repair Tool when DBCC CHECKDB shows any data corruption error. This utility has an advanced scanning technique that scans the highly corrupted primary (MDF) or secondary (NDF) file. And, recover the data object such as triggers, tables from it.
The great part of this software is user can export the recovered file in SQL Server Database or SQL Server Compatible SQL Scripts. Moreover, SQL Database Recovery Tool supports to repair corrupted Database of version 2017, 2016, 2014, 2012, 2008, 2008 R2, 2005, 2000.
Final Note
It is a head-scratching situation when the SQL database got corrupted and no relevant solution is not in the hand. So, after considering this painful situation, in this article, we have discussed about DBCC CHECKDB that reports error in the database. Along with this, three different solutions have illustrated to recover data from corrupted SQL database.
SQL Canada (SQLCAN) 
One comment