How to Recover Data from Log File in SQL Server – Step by Step

Problem:

We are using SQL Server 2012 standard edition. Today’s morning, When I opened my database, it was running fine and completely accessible. But, somewhere around 2:14 pm, I start facing problem in the database with following error message.

I consult with my administrator and we found that there is a corruption in .mdf file. The worst thing is that, this is my client database and I cannot afford to loose any single attribute of the database. Thus, I looked into the backup of current database, but, it was 13 days old. Can someone help me How do i recover data from log file in SQL Server?

Solution:

Having a healthy backup of a database is a relief to a SQL Server DBA. But, what if the backup is not available or the backup is damaged too? Then, what we do?

Fortunately, there’s a way to retrieve data from log file with the help of SQL Log Rescue tool. For this, SysTools SQL Log Analyzer Software is the best option. It is an authentic way to potentially analyze the transaction log file and restore the data from it. Plus, user can also check who deleted data from table in SQL Server. In addition, one can easily recover deleted records in SQL Server from LDF file. This tool provides flexibility to the user by retrieving the data directly into Live SQL Server Environment or in SQL Script. Moreover, it can restore data from log file in Microsoft SQL Server 2017 , 2016 , 2014 , 2012 , 2008 , 2005. Let’s check out how the tool works and view log file of SQL Server.

Steps to Recover Data from Log File in SQL Server

In the following segment, we are going to perform the recovery of database through transaction Log file. This can be done by following the below-mentioned steps:

Step 1: Download, Install and Launch SysTools SQL Log Anlyzer on Windows machine.

Download Free SQL Log Analyzer

Step 2: Click on Open button from the top left corner to add the database files (.ldf & .mdf) into the software.

Step 3: A new window will appear with Two different options to Add the LDF file into the software

Online Option: If you are going with Online option, then it will ask you for the SQL Server details like Server Name, Username & Password and Database Name, to which you want to read.

Offline Option: This option is used to read and recover database from offline SQL Server database files (.ldf & .mdf). If you are going with this option, then software will ask for .ldf and its associated .mdf file.

Like in the above scenario, we have to choose Offline option and Add the .ldf and its associated .mdf to the software by clicking on (…) button.

Step 4: Next, Click on OK button to proceed for the recovery of data from log file.

Step 5: After clicking on OK, It will prompt for a message that “Could not identify SQL Server version for the selected .mdf/.ldf file. Please select appropriate version of SQL Server”. Just click on OK .

Step 6: Now, select appropriate version of your SQL Server database files.

Step 7: In our case, the SQL Server version of corrupted database file is SQL server 2012, then again click on OK.

Step 8: After providing the accurate version of corrupted .mdf file, the software will scan the .ldf file and displays the transaction details (Insert, Update & Delete) after scan. Click on OK.

Step 9: Software will preview the transactions and its complete details in software preview pane.

Step 10: To preview the transactional details of a specific table, click on that table from the left side.

Step 11: Click on the respective checkbox of which table’s data you want to export and then click on Export button, to save the data direct to SQL Server.

Step 12: Click on respective checkbox to export selected transactions (Insert, Update, Delete)

Step 13: Apply Date Filter, If you want to retrieve the data from log file of a specific time interval.

Step 14: Choose the Export as SQL Server Database, If you want to save the recovered data direct to SQL Server. For this, you need to provide Server details like Server name, Username & Password.

Step 15: Select the destination database option as Create New Database or Export to Existing database. Click on Export to proceed.

Step 16: The database has been recovered successfully and exported to SQL Server database without any fail.

Final Thoughts!!

The disaster always come without any notice in SQL database. Due to which, you can loose your sensitive data. Therefore, in this article, we have discussed a solution that recover data from log file in SQL Server without losing any single bit of data.

Frequently Asked Questions-