Many times while working on SQL Server Database you have faced the problem of data corruption. However, being an administrator you must be aware of database recovery solution to recover corrupt MDF file. Therefore, in the upcoming section, we are going to describe the SQL database repair commands in a well-organized manner. Read on to know more in details about SQL database recovery commands.
Some Additional Information
You have to check the logical as well as physical integrity of all objects within the specified database by performing the below operations:
Note: DBCC CHECKDB command must be supported by the database. It can contain memory-optimized tables but validation has to occur only on disk-based tables. However, database backup and recovery has become a part and CHECKSUM validation is completed with memory-optimized file groups.
Now, the DBCC repair option is not available in memory-optimized tables, you need to back up your databases on a regular basis and testing the backup files. If data integrity problem occurs in the memory-optimized tables so you must recover from an older backup.
- Running the DBCC CHECKALLOC command on the database
- Running DBCC CHECKTABLE for all tables and view them in the database
- Running DBCC CHECKCATALOG script on the database
- On the database, validates the content of every indexed view
- Validation should be performed in link-level consistency between table metadata, file system directories and when saving varbinary(max) data in your file system using FILESTREAM
- Validates the Service Broker services within the database.
Description of All Commands
According to above script we are going to explain each syntax in a proper way. Let’s have a look:
- This field ‘database_name | database_id | 0’ indicates an argument. It is the database ID or name, which can run the integrity checks. If not mentioned, or if 0 is mentioned, then, the current database will be used. Database names must be observed for an identifier.
- It ‘NOINDEX’ specifies nonclustered indexes verification that will not be performed. It will decrease the overall execution time. NOINDEX will not affect the system tables because the integrity checks are always executed on all the system table indexes.
- It will indicate ‘REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD’ that repair level for the errors found by DBCC CHECKD. As a final option, use this REPAIR alternatives only. The specified database has been accessed in a single-user mode to utilize one of the following three repair options
- It signifies ‘REPAIR_ALLOW_DATA_LOSS’ to retrieve all reported errors. But, these repairs may cause some data loss.
- This argument ‘REPAIR_FAST’ helps to maintain backward compatibility only. There is no repair action will be performed.
- This argument ‘REPAIR_REBUILD’ is performing repair option, which has no possibility of any data loss. This may include quick repairs i.e., repairing missing rows in non-clustered indexes, and very time-consuming repairs, while rebuilding an index. It is not applicable to repair all errors involving FILESTREAM data.
- This argument ‘ALL_ERRORMSGS’ will display all reported error messages per object. All errors are displayed on the screen by default. This option has no effect if it is specified or ommitted as well. This error messages have to be sorted by object ID, except for those messages created from tempdb database.
- In this field ‘EXTENDED_LOGICAL_CHECKS’ if the compatibility level is 100 or above in SQL Server 2008 then, it performs the logical consistency checks on the XML indexes, indexed view, and spatial indexes, where exist
For more details, in the “Remarks” section you have to view “Performing Logical Consistency Checks on Indexes”, as far later
- This argument ‘NO_INFOMSGS’ contains all informational messages.
- This ‘TABLOCK’ may Cause DBCC CHECKDB command to obtain locks in place of using the internal database snapshot. This may include short-term an exclusive (X) lock on database. TABLOCK option causes the DBCC CHECKDB and will run faster on the database under heavy load, but reduces the type of concurrency effects on database while running the DBCC CHECKDB cmd.
- This field ‘ESTIMATEONLY’ shows an excessive amount of tempdb space, which is needed to run DBCC CHECKDB command with other specified options. The actual database test is not performed.
- It ‘PHYSICAL_ONLY’ indicates the limit to verify the integrity of physical page and records the header and then, allocate the consistency of the database. This option is available to provide a small check and it will check the physical consistency of SQL database. However, it can also detect the split pages, hardware failures, and some checksum failures that can compromise users data.
- This argument ‘DATA_PURITY’ Causes the DBCC CHECKDB for checking the database that is not valid or might be out-of-range. For example, DBCC CHECKDB detected the columns with time and date ranges that are larger than or less than the acceptable value for date & time data type; decimal or numeric data types with the precise or accurate value that is not valid.
- Enable the column-value integrity checks by default and no need to select DATA_PURITY option. In upgraded version of databases, column checks are not selected by default until DBCC CHECKDB command with DATA_PURITY has run error freely on the SQL database. After that, DBCC CHECKDB checks the integrity of column-value by default.
- It ‘MAXDOP’ can override the maximum degree of parallelism of sp_configure for the given statement. The MAXDOP exceeds the configured value with sp_configure. If MAXDOP can exceed the configured value with Resource Governor, then, SQL Database Engine uses the value of Resource Governor MAXDOP, which is also described in ALTER WORKLOAD GROUP.
Recovery of data in quite important for managing the SQL database. If one record is missing then, it will create a hurdle in the work flow. Therefore, we have already discussed the SQL database repair commands in an efficient manner.