For every SQL Database admin, the database corruption is like a nightmare and surely gives them a tough time. Often, they are the ones who need to take the final decision regarding database repairing, database restoration, etc. Like every industry, several myths exist among SQL DBAs also. Since such SQL Server myths and misconceptions can play a big role to influence their decision, the facts should be exposed before the database admins. This post will focus on some of the existing misconceptions around database repair. Read on this article to learn if what you know about database repair till now hold some truth or not.
SQL Server Myths and Misconception Regarding Database Repair
- Database Repair will not lead to data loss: There is no hard and fast rule about it. It will depend on the method of repairing. If you use REPAIR_ALLOW_DATA_LOSS, the data loss will become an inevitable part of it.
- Repair should be the default option: Obviously not. You need to understand the situation before taking any final decision. For example, if you are going to rebuild 1TB clustered index, you need to have an extra 1 TB space. Otherwise, the process will remain unsuccessful.
- Repairing is possible without running DBCC CHECKDB: All these consistency-checking commands have repair as the option: DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKDB. Remember that repair is not supported by DBCC CHECKFILEGROUP and DBCC CHECKCATALOG command.
- Repair command will fix every issue: No. Not every issue can be resolved by DBCC CHECKDB.
- Only Repair will set everything back to normal: This is a myth and therefore, not correct. After running repair command, you need to run DBCC CHECKDB to verify if repairing has been done properly. If any severe corruption prohibits deep checking, DBCC CHECKDB command can reveal more corruptions, even after repairing. It is also possible that the repair command may have deleted some data.
- Repair is a safe option for all system databases: Not at all. This option cannot be used in case of master database or tempbd. The reason is that repair command requires the database to be in single user mode and these two databases cannot be put in single user mode. This command will not have much impact on model, since model does not contain any user table and system table repairing is not possible. In case of msdb, it is feasible but may contain some side-effects.
- Online Database Repair is possible: There is no such thing as online repair when it comes to database repairing. You need to perform the repair in offline mode. Besides, the database needs to be in the single-user mode for repairing.
- REPAIR_REBUILD will resolve all problems: This is a far-fetched assumption. This REPAIR_REBUILD command will only solve issues with nonclustered indexes. Moreover, REPAIR_FAST option does nothing from 2005 onwards.
- Repairing process can be undone: Not always. In case of explicit transaction, rolling back is possible if you do not like it. But, not many people perform this. When it comes to EMERGENCY mode repair, roll back is not possible.
- Constrains can be fixed by Repair: No. It is not the task of repair command to fix constrains and neither it has that capability. For this reason, whenever you repair a database with constraints, run DBCC CHECKCONSTRAINT afterwards to check their validity.
- Repair command will save the data: No. Though this command will not delete data, it will not end up saving the data either in most of the cases. All it does is fix things quickly and correct if possible.
- Emergency Repair option will always work: This is not true. Do not depend upon Emergency Repair as a small broken component of the file-system can make it fail.
Despite being a dangerous process, database admins often have to choose the option of database repairing as the last option. For their sake, we have talked about SQL Server myths and misconceptions around database repair. We have attempted to reveal the facts about repair that uses should know before implementing it. If there is some corruption in MDF file, SQL MDF Repair tool is a proficient software that can be depended upon for fixing corruptions.