The transaction log file contains the SQL Server transactions of the database. It basically records the database transactions and the changes made by the transactions to the database. The logs are really useful to restore the data. However, sometimes, the transaction log files get deleted accidentally. Also, it can get corrupted due to virus infection, hardware problem, or various other reasons. If the transaction log file is corrupted or missing, you need to restore it. In this article, we’ll discuss how to rebuild SQL database when log file is corrupt.
Causes for Corrupt SQL Transactional Log Files
There are multiple reasons associated with corruption in Transactional Log files. Some of them are below:
- If you have mistakenly deleted essential data from the log files
- Incomplete I/O operations due to forced system shutdown
- Issues in the I/O subsystem of the hard disk on the system hosting SQL server
- Issues due to Incompatible SQL versions can corrupt log file
- Large-sized log files can result in a lack of space in the system and can corrupt the transactional log files
- Log files can be damaged due to malware or virus attacks on the system hosting the SQL server
If the SQL Server account permissions and configuration are modified incorrectly, it can impact the log files
Troubleshooting methods to rebuild SQL database if Log File gets corrupted
Follow the below methods:
Method 1-Rebuild the corrupt Log file
Step 1- Locate the log file.
You can execute the following query to know the location of data file and log file of each database.
SELECT name AS 'LogicalFileName',
physical_name AS 'FilePath'
FROM sys.master_files
Alternatively, you can right-click your database and select Properties. In the Properties window, go to the Files page. Here, you can find the path of the log file.
Step 2- Rebuild the log file
To rebuild the log file, you need to first detach your database or bring it offline.
You can run the below query to detach the database:
Alternatively, you can open the SQL Server Management Studio (SSMS), right-click your database, and select Tasks > Detach.
There is another way to set the database offline. The following command will help you to do that:
If you prefer to use SSMS, then right-click the database and select Tasks > Take Offline.
Once the database is disconnected, you can use the following command to rebuild the log file:
This command creates a database based on the data file (stellar.mdf). It also checks if the log file has errors. If it has errors, the log file will be rebuilt.
This code is used to add a new log file to the database using the alter database command.
Method 2-Recover Database from an Existing Backup
If you fail to rebuild the corrupted Log files, then you can recover the SQL database from an existing backup using the below steps:
- First launch ‘SQL Server Management Studio’ (SSMS).
- Right-click on the Databases and then click Restore Database.
- In the Restore Database window, under the General section, under source, specify the origin and location.
- Next, choose the affected database listed in the drop-down list.
- Click the Add option.
- Now click on the Backup Media list to select the required options.
- Click Ok.
- It will redirect you to the General window.
- Select the database you want to restore from the General page listed in the list box.
- Fill into the below sections under Destination.
- Database
- Restore to
- Click on the ‘Backup Sets to Restore’ to select the backups you want to restore.
- Click OK
An Alternative Solution - Use Stellar Repair for MS SQL
An alternative way to rebuild a corrupted database file in SQL Server is by using a third-party SQL database repair software, such as Stellar Repair for MS SQL. This is a special software designed to repair corrupt SQL Server database files. It can rebuild the database, including the log file.
This software repairs the database based on the SQL Server data file. It can restore tables, views, stored procedure, functions, and other SQL Server database objects. To use the software, you need to first detach the database or take it offline. You can also stop the SQL Server. However, it is strongly recommended to bring the database offline and not to stop the entire server for this task. The software also has the Include deleted records option to recover deleted data.
Conclusion
Above, we have discussed how to rebuild the SQL database with corrupted or deleted log file. To rebuild the database, you need to first detach the database or take it offline. Then, you can add a log file or rebuild it. However, the easiest option to repair the database with a new log is by using Stellar Repair for MS SQL. This software can rebuild a corrupted database with complete precision and integrity.
