{"id":93318,"date":"2022-01-13T11:01:26","date_gmt":"2022-01-13T11:01:26","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=93318"},"modified":"2025-04-10T10:02:29","modified_gmt":"2025-04-10T10:02:29","slug":"rebuild-sql-database-with-corrupted-or-deleted-log-file","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/rebuild-sql-database-with-corrupted-or-deleted-log-file\/","title":{"rendered":"How to Rebuild SQL Database with Corrupted or Deleted Log File?"},"content":{"rendered":"

Overview of the Issue<\/h2>

Every SQL database comprises of three file types viz. Main or Primary Database files (.mdf), Secondary database files (.ndf) and Transactional Log files (.ldf). While the main database files and secondary files are used to store database objects like tables, views, indexes, etc., the log files (.ldf) store the transactional logs and changes made by every transaction. The SQL server engine reads the complete log files and performs the recovery process when the SQL Server is started. However, if the reading or recovery process fails, the database remains offline, and it is marked ‘Suspect<\/a>’ or ‘Recovery Pending’ as per the stage at which failure occurred. This article will discuss the probable causes for corrupt log files, and then we will discuss the solutions to rebuild the SQL database.<\/p>

Reasons for Corrupt Transactional Log Files<\/h2>

The transaction log files are record files of the SQL database and are critical for recovery. Several reasons may render the log files in the SQL database corrupt. These reasons include:<\/p>

    \n
  1. Unexpected termination or forced system shutdown.<\/li>\n\n\n\n
  2. Malware or virus attacks on the system, causing file damage or making the database inaccessible.<\/li>\n\n\n\n
  3. Increased size of log files resulting in lack of free space and exceeded configured maximum file size.<\/li>\n\n\n\n
  4. Configuration issues with the I\/O subsystem used to host the system and user database files.<\/li>\n\n\n\n
  5. Changes in the SQL Server Account.<\/li>\n\n\n\n
  6. Accidental data deletion.<\/li>\n<\/ol>

    Ways to Rebuild SQL Database With Corrupt or Deleted Log Files<\/h2>

    Before we start with any method, it is important to check the SQL Server error logs and events logs and if you come across an error caused due to hardware issues, you need to get it repaired and see if the problem is resolved. If there is no hardware issue, start with the methods below:<\/p>

    Method 1 – Recover Database from an Existing Backup<\/strong><\/h3>

    To recover a database from an existing backup follow the steps described below:<\/p>

    Step 1: <\/strong>Launch the ‘SQL Server Management Studio’<\/strong> (SSMS).<\/p>

    \"open<\/figure>

    Step 2: <\/strong>Right-click on the Databases<\/strong> and select ‘Restore Database’<\/strong>.<\/p>

    \"restore<\/figure>

    Step 3:<\/strong> In the ‘Source’ <\/strong>section on ‘General’ <\/strong>page, specify the source and location.<\/p>

    Step 4: <\/strong>Select the database to be restored from the drop-down list.<\/p>

    Note:<\/strong> If you can’t find your intended database, it means the backup for it has not been created, and the current method can’t be used to repair your database.<\/p>

    Step 5: <\/strong>Click the […]<\/strong> button to open the ‘Select Backup Devices’ <\/strong>box and choose the ‘Backup Media Type’ <\/strong>from the drop-down list.<\/p>

    \"choose<\/figure>

    Step 6: <\/strong>Click ‘Add’<\/strong> to open a dialogue box describing what you can select to restore.<\/p>

    \"add<\/figure>

    Step 7: <\/strong>Once you are done with selecting the options in ‘Backup Media’<\/strong> list, click ‘OK’<\/strong> to return to the ‘General’<\/strong> page.<\/p>

    Step 8: <\/strong>Select the database name you want to restore from the list box.<\/p>

    Step 9:<\/strong> Leave the ‘Restore to’ <\/strong>option set at ‘To the last backup taken’ <\/strong>or select a backup of your choice from the ‘Timeline’ <\/strong>and click ‘OK’<\/strong>.<\/p>

    \"database<\/figure>

    Step 10<\/strong>: Select the backups to restore from the ‘Backup Sets to Restore’ <\/strong>grid and click ‘OK’<\/strong>.<\/p>

    Method 2 – Rebuild Damaged or Corrupted Transaction Log file<\/h3>

    To rebuild a corrupt or damaged SQL Server log file, you need to put the database in Emergency mode. First, make sure that your data has backup and follow the steps below:<\/p>

    Step 1: <\/strong>Use the below command to set your database in ‘Emergency Mode’.<\/strong><\/p>

    USE master\nGO\nALTER DATABASE [Database_name] SET EMERGENCY\nGO\nALTER DATABASE [Database_name] SET SINGLE_USER\nGO<\/code><\/pre>
    Step 2: <\/strong>Now that the database is set to Single User mode, execute the DBCC CHECKDB<\/strong> command with REPAIR_ALLOW_DATA_LOSS <\/strong>option.<\/p>
    DBCC CHECKDB ([DATABASE_NAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL ERRORMSGS;\nGO<\/code><\/pre>
    Caution<\/strong>! It is important to know that you might lose data while executing the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option.<\/p>
    \n
    Note: <\/strong>If the corruption still exists, use the following query to rebuild the log file. Before running this query, put the database to Offline Mode and change the name of the corrupt log file.<\/p>\n<\/blockquote>
    ALTER DATABASE [original_log_file_name] REBUILD LOG ON (NAME= logicalname, FILENAME=’C:\\Program Files\\Microsoft SQL Server\\MSSQLn.MSSQLSERVER\\MSSQL\\DATA\\file_name.ldf’)<\/code><\/pre>
    If this method seems a bit difficult and you wish to adopt an easier and effective method then follow the next method.<\/p>
    Method 3 – Repair the SQL Database Using Stellar Repair for MS SQL<\/h3>
    Use a SQL Repair tool<\/a> like Stellar Repair for MS SQL to restore the database without any complexities. The software uses advanced algorithms to repair corrupt MDF and NDF files. Once repaired, a new transaction log file (.ldf) is created. Follow the steps below to repair and rebuild your SQL log file:<\/p>
    Step 1:<\/strong> Download the Stellar Repair for MS SQL.<\/p>
    \n
    \"Stellar\"<\/a><\/figure>\n<\/div>
    Step 2: <\/strong>Once download is complete, run the software. This opens a ‘Select Database’ <\/strong>window with options to browse or find the corrupt MDF file.<\/p>
    \"options<\/figure>
    Step 3: <\/strong>Once you have located the file click the ‘Repair’ <\/strong>tab at the bottom right corner of the window to start the repairing process.<\/p>
    Step 4:<\/strong> A preview of the repaired database and its components get displayed.<\/p>
    \"preview<\/figure>
    Step 5:<\/strong> After verifying the data, click ‘Save’<\/strong>.<\/p>
    \"save<\/figure>
    Step 6: <\/strong>To proceed with the repaired database saving process, choose any of these options: <\/p>