Last Option to Fix Database Corruption After a Backup Failure

SQL Database Corruption is the worst thing any SQL DBA or user faces. Corruption in SQL Server is caused due to number of reasons. The reasons could be the IO subsystem, storage subsystem, sudden shutdown etc.You can simply find out the reason of corruption in error logs or can recognize it by checking its symptoms. A database becomes inaccessible, when it is corrupted.

When a DBA/user faces corruption in SQL Server Database he tried to repair it by restoring database from backup. But Wait! What if you dont have backup?
Dont Worry Here we are also going to discuss the solution to repair SQL Database corruption if you dont have backup. Also covers the last option to fix Database corruption when all the methods get fail.

How to Fix SQL Database Corruption

There are different ways by which you can repair SQL Database corruption. Below are the options:</p

• Restore database from backup
• Rebuilding through Transaction logs
• By Repair Commands
• Last option to fix Database corruption – Alternate Approach

How to Restore Database from Backup File in SQL Server

Follow the below steps to restore database from .bak file in SQL Server:

1. Open SSMS and Click on the Databases.
2. Right click on the Databases –> Restore Database.
3. In “Source for restore” section, Click From Device radio button and click to browse the file.
4. In “Specify backup” window, click Add to load the file. Click Ok.
5. In Destination for restore section, select the database you want to restore. Select the backup set by clicking on checkbox.
6. Click on the Options present at left pane and select Overwrite the existing database (WITH REPLACE), in Restore options and Click on the second tab(RESTORE WITH NORECOVERY) in Recovery state option.
7. Click Ok to perform restore.

The above process can be done if you have backup. But What if you dont have backup? Dont worry, Now we will going to discuss two different methods to repair SQL Database corruption if you dont have SQL database backup.

How to Rebuild Transaction Log in SQL Server

You can recover SQL Database corruption by rebuilding through transaction logs. Run DBCC_REBUILD_LOG. It rebuilds the log file and fix the corruption issue. For this, you have to put SQL Server to emergency recovery mode.

ALTER DATABASE  SET EMERGENCY, SINGLE_USER
 GO

ALTER DATABASE  REBUILD LOG ON (NAME=, FILENAME='')
 GO

Now put the database in MULTI_USER ONLINE Mode and run DBCC CHECKDB to find out if there is an issue.

Fix SQL Corrupt Database by Repair Commands

You can also repair SQL Database corruption by running database Repair commands. Run DBCC CHECKDB, it will give you the corruption errors. DBCC offers you two repair commands to fix SQL Database corruption ie REPAIR_REBUILD & REPAIR_ALLOW_DATA_LOSS.

REPAIR_REBUILD: It does not lead to data loss while repairing corrupted database and quickly repair missing rows in non-clustered indexes, rebuilding an index. It does not involve repairing File Stream data.
Whereas REPAIR_ALLOW_DATA_LOSS brings database in consistent state but can lead to data loss.

Run DBCC CHECKDB to check the consistency of the database, and according to the errors Run the repair command.

ALTER DATABASE ‘db_name’ SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BEGIN TRANSACTION;
DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE db_name SET MULTI_USER;

Alert!!!

REPAIR_ALLOW_DATA_LOSS is the minimum level of corruption. If run successfully, it brings database in consistent state but leads to data loss. REPAIR_ALLOW_DATA_LOSS option should be the last resort option to repair SQL Database corruption.

Last Option To Fix SQL Database Corruption

If above methods get failed to repair SQL Database corruption, then you have an alternative option ie SQL Repair Tool as an last option to fix SQL Database corruption. The tool recover corrupted MDF & NDF database file of any file size. Not only this, you can be able to recover deleted records of SQL Server 2017, 2016 & its below version

Conclusion

SQL Database corruption is one the scariest thing any user or SQL DBA faces. Repairing it is the major tast. The blog covers such repair tasks when SQL Database get corrupted. It also covers the method when user is in wors case ie. If he does nit have Backup. User can go for an alternative option as last option to fix database corruption.