How to Recover Deleted Tables in SQL Server 2014/2012/2008 R2

The most needed SQL Log recovery functionality to be added, undoubtedly: “The ability to recover a single table from a database backup”.

Of course, it is possible to recover deleted data of a specific tables in SQL Server Database by success-full SQL Log Recovery, Easy way: One could restore database from full Backup. If , no changes occurred after the DELETE, TRUNCATE or DROP.

Or Use the full database backup to export the data and import it into the production database. This is a lengthy process and if any mistake done one may loss whole data. But using manual solution you cannot do it without restoring the entire backup or with the Transaction Log. AND If you don’t have a full database backup that contains the lost data

  • You cannot take the database offline
  • Some transactions were made on the database after the data was lost.

Which probably makes it become impossible to recover deleted data from tables within SQL Server. But here we will discuss how to rollback deleted data in SQL Server 2005 / 2008 / 2008R2 /2012 / 2014 / 2016 with the help of LDF file by SQL Log Recovery which is present in unreadable format.

Let us have a look how data is stored within SQL Server transaction log file and how to recover deleted single table, As per my research I found “Records within SQL log file are uniquely identified by a log sequence number (LSN). These LSN of a log record are important as all events within transaction log file can be useful for constructing correct restoration. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.” So by performing SQL Log Recovery this could be possible.

Steps to Recover deleted data in SQL Server 2014 / 2012 / 2008 R2 / 2008 / 2005/ 2002 /2000.

The SQL Server transaction log contains the information about the transactions made to your database SysTools Log Analyzer is capable to read this log file and recover or rollback deleted, dropped and lost data. To recover deleted tables data in SQL Server follow the steps:

  • Download and Install SysTools SQL Log Recovery Tool
  • Click on ‘Open’ for adding .ldf/.mdf file to the SQL Transaction Log reader software.

Here user will get two options to add file to the SQL Log analyzer software and recover SQL Log data in order to read Log file data with deleted records within SQL Server database:

  • Online DB Option
  • Offline DB Option

Offline Database Option

If users select Offline DB Option, click on the LDF File Path Browse button to load the SQL Log file.

  • Select the LDF file & click on Open to recover SQL Server logs.
  • Click on Fetch User to retrieve all the users of Server. After filling complete Data have a look to know in details:
  • SQL Log Recovery Tool starts Scanning. After scan completes windows screen prompt.
  • Now you will able to preview whole transaction performed.
  • Select the records you need to recovered at the preferred destination location and click Ok to Export complete transaction
  • Now you can see the complete export report and click on Ok.

Conclusion

With above steps user can easily recover deleted data in SQL Server version 2014 / 2012 / 2008 / 2005 / 2002 and all below version by making SQL Log Recovery possible. Two methods are define to recover deleted tables. Offline method is described in details to go through online recovery process visit: https://www.systoolsgroup.com/sql-log-analyzer/how-it-works.html