If you are working
as a SQL server DBA from a long time then you will have encountered
the SQL database corruption once in a while of your DBA life. Often
corruption occurs due to power failure, sudden
shutdown to the SQL server, hardware malfunction, virus attack, and
many more. You can't access the database until repaired the database
files.
Additionally, there
are several methods available to protect your database file from
being corrupt. And it is equally related to your job. In the event of
database corruption, the first and foremost question, have you
maintained the backup of database? If you do not have backup of
database then it is like a soldier
had gone to the country's border to protect the country without arms.
Soldier may die at the border but your database can't die if you have
not the backup of your database. You have chance to recover your
database by repairing it.
How to repair?
You can repair corrupt SQL
server database by following methods:
1. Repair the database
using DBCC CHECKDB command
DBCC CHECKDB is the best
command to test and fix the consistency errors in the SQL server
database. DBCC CHECKDB is a combination of three commands: DBCC
CHECKCATALOG, DBCC CHECKTABLE, and DBCC CHECKALLOC. If you run DBCC
CHECKDB against the database then no need to run all other three
commands separately. This command has three options to repair a
corrupt SQL server database and the commands are repair_fast,
repair_rebuild, and repair_allow_database. There is no any data loss,
when you run DBCC CHECKDB with repair_fast & repair_rebuild but
you may loss some amount of data in repair_allow_data_loss option.
Here is the syntax of DBCC
CHECKDB
DBCC CHECKDB
[
[ ( your_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 } ]
}
]
]
Note:
Don't run DBCC CHECKDB with repair_allow_data_loss against the
database without knowing the effect of it.
2. Repair the
database using 3rd
party SQL repair utility
There are so many
data recovery companies available out there that offer SQL recovery utility to repair corrupt SQL server database. Let’s try any of the
utility and see what they can do for you.
Note:
First download the demo version of any software to measure the
functionalities of the software.
