How I Use DBCC CHECKDB Command to Repair My SQL Database

For those who are wondering how to use the DBCC CHECKDB to repair the database in SQL Server, you can run the syntax below:

    [ ( database_name | database_id | 0   

        [ , NOINDEX    

        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]   

    ) ]   

    [ WITH    

        {   

            [ ALL_ERRORMSGS ]   

            [ , EXTENDED_LOGICAL_CHECKS ]    

            [ , NO_INFOMSGS ]   

            [ , TABLOCK ]   

            [ , ESTIMATEONLY ]   

            [ , { PHYSICAL_ONLY | DATA_PURITY } ]   

            [ , MAXDOP  = number_of_processors ]   

        }   

    ]   

] 

If you are not really familiar with all the terms included, here is the explanation:

  • database_name | database_id | 0 is the name of the ID of the database for which to run DBCC CHECKDB repair. The command will be applied to the current database by default if not specified or if 0 is specified.
  • NOINDEX means that intensive checks of nonclustered indexes for user tables do not need to be executed. This one reduces the total execution time. The system tables will not be affected by it because the integrity checks are always performed on the system table indexes.

Repairing one of the options mentioned above means allowing DBCC CHECKDB to repair the found errors. In order to understand the different between all the options of repair, here is the explanation:

  • REPAIR_ALLOW_DATA_LOSS: For those who use this repair option, DBCC CHECKDB will try to repair the errors found. This one may cause data loss if done successfully.
  • REAPIR_FAST: This option will not do any repair action. Instead, it will only maintain the syntax for backward compatibility.
  • REPAIR_BUILD: If you select this option, repair actions will be performed but no data loss will occur. There are two kinds of repairs included in this option, the first one is quick repair and the second one is deep repair.

However, as suggested by Microsoft, you can use the repair options only as the last option. They explain that when there are errors reported by the DBCC CHECKDB, the most effective option recommended is to restore the database from the last known good backup. As for the REPAIR_ALLOW_DATA_LOSS, it is not an alternative for restoring from a backup. This one is only recommended when there is no backup available.

What should you do if you want to repair the database without CHECKDB repair option? Is there any other choice than REPAIR_ALLOW_DATA_LOSS when you cannot repair the database from a backup? The answer to the question is yes. REPAIR_ALLOW_DATA_LOSS is not the only choice. Aside from it, you can repair your corrupted database using the SQL database recovery software called EaseUS MS SQL Recovery. EaseUS MS SQL Recovery can be applied to repair database log files that may cause in database errors; repair SQL Server database, both mainly (.mdf) and secondary (.ndf); repair corrupted SQL server database projects such as indexes, keys, triggers, tables, rules, and stored procedures; and recover the deleted or dropped SQL database records.

There are three steps to be able to repair a database: choosing the corrupted database for recovery, repairing the corrupted database, and exporting to the database or as scripts.

In order to choose the corrupted database for recovery, the first thing that you need to do is to launch EaseUS MS SQL Recovery. Then, choose the corrupted database file by clicking Browse (the two dots) or Search. After you have selected the file, click the Repair button to start the analyzing process. Please take a note that to use this software, you will need to stop the SQL Server service.

To repair the corrupted database, the software will show the recoverable items in a three-line structure. They are all shown in a left pane. You will have to choose the component that you want to be recovered. From the window, please click the Export button.

If you want to export to the database or as scripts, first of all, you will have to select to export the database objects to the database or export the items as scripts. You can enter the information needed and select the target database if you choose Export to database. Doing so will open a window asking you to provide credentials to connect to the server and the destination to save the recovered items. In order to start the process of repairing, please click OK. Remember that you will have to restart the SQL Server service before clicking OK.

DBCC CHECDB is the common option for those who want to repair their database in SQL Server. However, as stated before, it is not the only choice. If this one fails to work or you do not want to use the repair option called REPAIR_ALLOW_DATA_LOSS, you are able to use EaseUS MS SQL Recovery that has the ability to repair your database as the alternative option.

If you face some difficulties while you are using the DBCC CHECKDB command to repair your SQL database or you have anything to ask, do not hesitate to visit the community related to the SQL and seek a help from the members of the community so that they can help you on fixing the issue or on finding the answer to your question.

DBCC CHECKDB, which is also known as Database Console Command CHECKDB, refers to the one thing that can be used to check both physical and logical integrity of objects such as views, tables, clusters, indexes, sequences, and synonyms in a SQL Server database or Azure SQL Database. This one is normally used to repair database corruption. Running DBCC CHECKDB means executing the repair options below:

  • Run DBCC CHECKCATALOG:
  • Run DBCC CHECKALLOC:
  • Run DBCC CHECKTABLE:
  • Verify the contents of each indexed view in the certain database.
  • Verify the Service Broker data in the database.
  • Verify link level consistency between file system directories, table metadata, and files when saving varbinary (max) data in the file system using FILESTREAM.

In conclusion, it is not important to run DBCC CHECKALLOC, DBCC CHECKCATALOG, or DBCC CHECKATBLE separately from DBCC CHECKDB.

Related Posts