Wednesday, March 27, 2013

Need to repair corrupt SQL server database


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.

No comments:

Post a Comment