Recover from a corrupted transaction log file

Avatar    Oswald Foster     Updated On - 20 Mar 2019

MS SQL Server is the most ordinarily used database client, can be subjected to corruption due to various logical and physical reasons. Let us suppose a situation when you are informed that one of the transaction log file has been corrupt due to some unknown reasons. Because of the transaction log file damage, SQL Server finds it difficult to access the required .MDF file.

It means .MDF file requires recovery, which can be easily done with the backup of MDF file you have. However, in case you do not have backup of the MDF file, SP_ATTACH_DB_SINGLE_FILE_DB command can be a help for you. The command helps you rebuild the database but only in following two ways:

Use the SP_ATTACH_DB_SINGLE_FILE_DB command on MDF file that were previously detached from the server through SP_DETACH_DB command.
Use the SP_ATTACH_DB_SINGLE_FILE_DB command on MDF file having only one log file.

In case, you do not have any backup of MDF file and also the SP_ATTACH_DB_SINGLE_FILE_DB command does not help you rebuild the database then execute the following steps (on your own risk) to restore the database:

1. Create a backup of the MDF file.
2. Detach the corrupted database from server using SP_DETACH_DB command.
3. Create a new database file having the same name as the damaged database file. The file should have same path where the log files are stored.
4. Stop SQL Server services.
5. Rename the newly created database file without .MDF extension.
6. Rename the damaged database file as the newly created database file.
7. Start SQL Server services.
8. Open up Query Analyzer and execute SP_CONFIGURE allow update, 1. You are suggested to use the SP_CONFIGURE system stored procedure with RECONFIGURE WITH OVERRIDE statement, if you want the process to take effect immediately without stopping or restarting the Server.
9. Now, update the SYSDATBASES system table by finding DBID settings to 32768 for corrupted database file.
10. Restart SQL Server services.
Note: The above mentioned steps are very tricky and could result into data loss. It is recommended that users should perform them in test environment at their own risk.

The executed steps will repair the damaged MDF file and you will be able to work with it. However, in case these steps do not work, you need to take help of MDF recovery and repair software. Kernel for SQL is well designed and easy to use sql server data recovery tool. The software recovers database objects like tables, triggers, view, etc. from damaged MDF file created in SQL Server 2000 or SQL Server 2005.

Let us know the working of Kernel for SQL Database Recovery through these steps:

  1. Start the installed software application and add MDF file for repair using the Browse button. Select any recovery mode as per requirement, SQL database version and click Recover.
  2. Installed the software and add MDF file for repairing

  3. The SQL database objects will get retrieved. Click on any object to view its complete preview. Then make the selection of data and click Save.
  4. Check Preview

  5. Choose the saving option – either to live SQL Server or Batch file mode at system location. Provide the details or saving path and click OK.
  6. Select Saving Mode

  7. The saving process will start and on completion shows a message. Click OK to finish the process.
  8. Repaired SQL database saved successfully

Thus, recovering data from the corrupt SQL database is an easy affair for Kernel for SQL Database Recovery software. It recovers all database objects quite smoothly.

Conclusion

We have discussed a condition where a transaction log file of the SQL Database gets corrupted. For recovery, manual solutions like the use of backup (if available) and rebuilding of the database to restore the database (if backup not available) are explained. The final solution of recovery through efficient third-party tool Kernel for SQL Database recovery is explained through screenshots.