How to Recover Corrupted SQL Database

Microsoft SQL Server is one of the most popular database, which is the largest used by organizations. Some time, we face database corruption issue and more than 95% of DB corruption happens due to hardware failure but some other reason could be responsible for database corruption.Database

  • Bugs in SQL server
  • Improper storage
  • Unexpected system shutdown while the database is opened
  • Changes in SQL account
  • Virus Attack

Tips to repair corrupted database

It’s a very panic situation to deal with corrupted databases in SQL Server. Here is one solution to repair corrupted database but not with guarantee.

Step1:- Check database through command and analyze whole report

DBCC CHECKDB

Step2:- Analyze the Table name, which has corruption of page / index or row.

 dbcc checktable(‘Table name’)

if any corruption result generated on check table then run below command

Step3:- Convert DB into Single user mode

ALTER DATABASE “db name” SET SINGLE_USER WITH ROLLBACK IMMEDIATE

run below command

dbcc checktable(‘table name’), REPAIR_REBUILD)

If not resolved with step 2 then process step4

Step4:- execute update usage command

Note: – But update usage command is very risky command and it’s avoidable, make this command in test environment first then try to implement on production setup.

dbcc updateusage(‘Table Name’)

Update usage command clear all pages and allocation of index and table and reallocate it in disk, this command SHOULD be run only one time per TABLE / DATABASE.

Step4:- if all above 3 command not works, it’s happen only data corruption situation (5% chances) then last option is to REPAIR THE TABLE with ALLOW DATA LOSS.  Please run below command, But make the table backup in any EXCEL / another table or any another TMP table before this command and Also verify COUNT level of row before implement and after implementation, if both are same then it’s OK otherwise you will get the data loss on corrupted row

dbcc checktable(“table name”, REPAIR_ALLOW_DATA_LOSS)

Database

You Must Also Read

DCOM got error “1068” attempting to start the service netprofm with arguments “Unavailable” in order to run the server

Leave a Reply

Your email address will not be published. Required fields are marked *