Easy ways to perform maximum recovery after your SQL Database has been corrupt!!

Easy ways to perform maximum recovery after your SQL Database has been corrupt!!

In this article, we will explain the easy and best possible ways to perform recovery after SQL Database has been corrupt.

The role of DBA has become more and crucial nowadays.

With the increase in virus attacks and ransomware attacks, the SQL database has become more and more one to database corruption. 

This can lead to users losing their crucial database files and data.

It is a very difficult task to prevent all the possible attacks by hackers or/and viruses/malware/ransomware etc. Also, in the scenario, if the database is heavily used, it can cause hardware failure. All these issues can compound to severe database corruption and can cause a halt in the day to day business of any enterprise. 

Best possible workarounds to perform maximum recovery after your SQL Database has been corrupt. 

In my opinion, you can try the following workarounds if the issue of SQL database corruption comes up.

Workaround 1: Check for the availability of backups

If you have created a backup earlier, then you can use it to restore the database. 

Note: Usually there is loss of some data according to the backup date.

For example, If the last backup was made 6 days ago, you would lose the data of the last 6 days. It is possible to recover data of a specific hour if you take a transaction log backup earlier. However, in case of database corruption, it will not be possible This is the reason why you need to plan on taking regular backups. You can try keeping backups in a combination of transaction log backups, full backups, and differential backups if necessary.

You can use an option “Verify only “ to verify if the backup is working fine.

Also, keeping a backup at a different location is good practice also, because if the database and backup are present on the single drive, then both can be lost in case of virus attack.  

Workaround 2: Repair a corrupt database using inbuilt options

This is for that scenario where there is no backup present. In this case, you need to repair the SQL database.

Restoring the database may take longer if your database is huge having several TB in size and it will take less time fast if the database with few MB.

You can use the DBCC command in SSMS or any other SQL Server tool of your preference. This command will show you the error messages. 

If you have to repair a big database, you can use the TABLOCK option or NO_INFOMSGS to run the command easily. However, access to the database will be slower because a lock will be produced by the command. So, this will take a few more minutes.

If your database is partially corrupt, you can use the DBCC CHECKTABLE to repair specific tables in the database.

DBCC CHECKDB command uses a lot of space in the TEMPDB database, so you can use the option ESTIMATE ONLY to estimate the space required on the TEMPDB especially when the database is big.

DBCC command can be used to repair using different options. One of the options is REPAIR_BUILD and, if this fails then you can use the REPAIR_ALLOW_DATA_LOSS option, that will repair but lose some data if necessary.

<span style="color: #3366ff;">DBCC CHECKDB ('</span>databasename<span style="color: #3366ff;">', REPAIR_ALLOW_DATA_LOSS)</span>

Workaround 3: Repair and restore SQL database using SQL recovery tool 

If you do not have a backup or the DBCC CHECKDB cannot repair your database, you can use the MS SQL Recovery Tool. This software will repair the corrupt SQL database effectively and in no time.

This software saves recovered data either in SQL Server database format or SQL Server compatible script format.

It ensures repair of corrupt MDF and NDF files of SQL Server database along with supporting SQL Server ROW-compression and PAGE-compression.

Conclusion:

In this article, I have explained the best possible workarounds that you can use to repair and restore SQL database that has been corrupted. You can make your choice according to the extent and level of corruption in the SQL Database while executing the solutions provided in the article.