Recover from a corrupted transaction log file

   Oswald Foster     Updated On - 23 Jul 2018

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.