Recover Deleted Data From SQL Server Table by Transaction Logs

The task of creating tables, storing data in records look quite easy to SQL Server users. But if the data is being deleted by mistake or because of some other hardware or software issues, then the situation becomes complex. Recovery of deleted data is not a child’s play. So, considering this issue we have come up with this write-up which will help you to know various methods to answer your query how to recover deleted data from SQL server table by transaction logs? Let’s begin with a detailed discussion on the same.

Techniques to Rely On For Recovering The Deleted Data From Server:

1. Manual Method: – Using LSNs (Log Sequence Numbers), but it works only if the time of deletion is known to the user.
2. Automated Solution: – Simple yet secure and reliable solution for recovering deleted data from the server by using SysTools SQL MDF Database Recovery.

Know-How to Recover Deleted Data From SQL Server Table by Transaction Logs

Deleted Records’ Recovery Using SQL Server LSN:- In SQL Server transaction logs, the LSN(Log Sequence Number) is nothing but unique identifiers assigned to each record. Here we can restore the deleted rows of SQL tables if the time when the record was deleted is known.

User has to be ensured that the Full Recovery Model or Logged Recovery Model were created when the data was actually deleted for starting the recovery process. This is the prerequisite for the successful recovery of the deleted records.

The steps are described below to recover the deleted data from SQL Server 2016, 2015, 2014, 2012, 2008 and 2005.

Step 1: Fire the following query to know the total number of records in a table where from th record is being deleted.

Select * From Table_Name
Step 2: Next, run the procedure to take log back using the below-mentioned query:
USE NameOfTheDatabase
GO
BACKUP LOG (NameOfTheDatabase)
TO DISK = N’D:\ NameOfTheDatabase\RDDTrLog.trn’
WITH NOFORMAT, NOINIT,
NAME = N’NameOfTheDatabase-Transaction Log Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Step 3: Information has to be collected from the SQL Server table about the deleted records for data recovery. This query will retrieve Transaction ID of the deleted records.
USE NameOfTheDatabase
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’

Step 4: Execute the query given below to know at what time exactly the records get deleted.
USE NameOfTheDatabase
GO
SELECT
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3′
AND
[Operation] = ‘LOP_BEGIN_XACT’

Ongoing LSN you will be able to find now query.
Step 5: Restore process has to be run to restore the deleted data from the SQL Server Table.
Recover Deleted D USE NameOfTheDatabase
GO
RESTORE DATABASE NameOfTheDatabase _COPY FROM
DISK = ‘D:\ NameOfTheDatabase \RDDFull.bak’
WITH
MOVE ‘NameOfTheDatabase’ TO ‘D:\RecoverDB\ NameOfTheDatabase.mdf’,
MOVE ‘NameOfTheDatabase _log’ TO ‘D:\RecoverDB\ NameOfTheDatabase_log.ldf’,
REPLACE, NORECOVERY;
GO
Step 6: Now is the time to verify if deleted records are recovered or not.

Efficient Way to Recover Deleted Records From SQL Server 2017 / 2016 / 2014

If you failed to recover deleted data from SQL server table by transaction logs then you can take the help SQL Database Recovery software. This software provides you the option to recover deleted records from SQL server table. Also by using this software, the user can preview accidentally deleted SQL tables records in red color. The user can easily recover database objects such as tables, functions, stored procedure. Moreover, This application is compatible with SQL server 2017 and its below version.

download

Follow The Steps to Recover Deleted Records From SQL Server Table

1. Download and Install the software on your machine.
2. Click on Add file button and add the MDF file in the software.
3. Now choose the Scan option and select the SQL server version.
4. Check the option preview deleted SQL database records in red color.
5. Preview the SQL server database items. The software will preview the deleted SQL table records in red color.
6. And click on Export button to Export the SQL database.
7. Now in database authentication choose the server name and the authentication mode.
8. Now choose the destination database
9. Check the Database items you want to export.
10. Choose the option from with only schema and schema and data.
11. Mark the option Export deleted records and finally click on Export button.

Final Words

In this article, we have discussed how to recover deleted data from the SQL server table by transaction logs. The manual solution is quite lengthy and difficult to perform. It requires strong technical knowledge. So it is better to take the help of SQL database recovery tool to recover deleted records easily.

SQL Server Latest Updates (Nov. Dec. 2018)

Directly from the SQL Server Release Service blog, here the latest updates for SQL Server 2016 SP1, SP2, 2017 RTM and 2014 SP2, SP3:

Cumulative Update #1 for SQL Server 2014 SP3

Cumulative Update #15 for SQL Server 2014 SP2

Cumulative Update #12 for SQL Server 2016 SP1

Cumulative Update #4 for SQL Server 2016 SP2

Cumulative Update #13 for SQL Server 2017 RTM

and

Public Preview for SSRS 2017+ Management Pack with Power BI Reporting Server Support

…Stay Tuned, Merry Christmas and a Happy New Year! 🙂

SQL Server Latest Updates (Aug. Sep. 2018)

Directly from the SQL Server Release Service blog, here the latest updates for SQL Server 2016 SP1, SP2, 2017 RTM and 2014 SP2:

Cumulative Update #13 for SQL Server 2014 SP2

Cumulative Update #11 for SQL Server 2016 SP1

Cumulative Update #3 for SQL Server 2016 SP2

Cumulative Update #11 for SQL Server 2017 RTM

…Stay Tuned! 🙂

Wrong version of ISDeploymentWizard in SSMS 17.8.1

The problem

Years ago, with SQL Server 2016 release, Microsoft came up with a separated brand new version of SQL Server Management Studio. It’s been a happy day for the SQL Server community and database developers.

Shortly afterwards, our company started to migrate every instances from older version of SQL Server to the 2016, using SSMS 17.*. Developers have already jumped into Visual Studio 2017 and everything seemed to work like a charm, until we started deploying integration services via the new SSMS, after we converted them to 2016 TargetServerVersion (which is NOT the Project Version).

The TargetServerVersion is the SSIS version, also for the deploy operations, while the Project Version setting tells to Visual Studio how to open projects based on .dtproj specifications on the XML projects definitions.

Some days ago I’ve realized that after updating to the latest build of SSMS (17.8.1), the .ispac deployment is actually executing the latest build of the Integration Services Deployment wizard (aka ISDeploymentWizard.exe). As a result every deploy of .ispac files, regardless trying double click or deploying directly from the Integration Services Catalog, the SSISDB, failed with one of the most scaring error message ever:

“[…] The Script Task <unique_name> uses version 15.0 script that is not supported in this release of Integration Services […]

What? Why? I’ve a workstation with SQL Server 2016 and the related Integration Services 13.0:

blog_ssis_error_001

The computer I’m speaking of has the same build of mine, nobody has installed any other Integration Services versions anywhere. Andy Leonard explained this behavior in this blog post. Unfortunately, in my scenario, I cannot solve the problem.

Let’s try to explain better.

Scenario

The workstation I’m working on has SQL Server 2016 (build 13.0.5149.0) and Integration Services 13.0 on Windows 10 Pro. I’ve got a simple package with a single script task which does literally anything:
blog_ssis_error_002bAs Andy suggested us, I’ve changed the TargetServerVersion to SQL Server 2016, so I’ve got C# 2015 compiler for scripts:

First execution and deploy

Executing it locally, nothing happens, but it happens in a green way (success):

blog_ssis_error_002a.PNG

Ok, now we’re going to deploy to the other machine, generating the .ispac file and double clickin on it. It’s important to make sure that you’re double-cliking the .ispac file. Do not right click on the Integration Service Catalog project folders because SSMS will execute the latest build of ISDeploymentWizard.exe by design.

blog_ssis_error_008b
deploy from SSMS 17.8.1

 

When double clicking, the app selector should use the version related to the TargetServerVersion setting of the .ispac. This works for many of my other computers. But for one of them, here is the screen:

blog_ssis_error_005
double click on .ispac file

Hey! This is 2017 also when clicking on .ispac file. Let’s try to deploy using the new tool. The deploy succeeded.

blog_ssis_error_006.PNG

And now, let’s try to validate the package execution via SSISDB, right clicking on the project itself and selecting “Validate…”. This is the result of the empty script package validation:

blog_ssis_error_007.PNG

I’ve tried on six different machines, five ran successfully and ONLY ONE returned the above error message. Still stuck in the middle.

What have we changed?

Just the setup of SSMS (17.8.1) updating the 17.7 one. Once again, the same setup on all six machines. Five by six worked, this one is trying to kill me.

For some strange reason, something (I assure you all, not someone) has changed the registry in the .ispac application association, maybe when double clicking for the first time the file in a pending reboot (?). We’re still investigating, since we used to avoid any change in production without permissions and processes. That said, it’s weird. And it was so difficult to get.

Solution

Easy to say, now that we’ve figured out the root consequence. Not so good, but changing the registry on the key HKEY_CLASSES_ROOT\.ispac with the 130 executable (IntegrationServices.ProjectDeploymentFile.130) fixed the unwanted behavior. The key has been set to IntegrationServices.ProjectDeploymentFile.140 right after the update from 17.7 and 17.8.1.

Instead of changing it via regedit, you can try an “open with…” with “use default” checked in order to force the association between .ispac file and the right ISDeploymentWizard.exe version. But this time, in this machine, it didn’t work. This is the reason why I tried the regedit action.

Conclusions

I have to say a big thanks to Andrea Amantini, one of my peer, which is well known for his ability to find out “a needle in a haystack.”. Strange things happened here. A combination of Murphy’s law, a sort of “black” friday and a pending reboot. Hopefully this helps someone, at least.

SysTools SQL Transaction Log Reader 5.0 : Know What is New

The SQL Transaction Log reader is a standalone utility, which helps to view SQL server transaction log files without any difficulty. It is capable enough to preview LDF activity, including Transaction, Time, Name, Query and Table Name.

Moreover, it allows a user to fetch and display all records from the live database. This application offers an option to filter and export as SQL database, as SQL script or CSV file format. It supports to analyze one or more NDF data files without losing a single bit of data. Also, it is compatible with all latest Windows versions, including Windows 10 and its below versions.

SQL Transaction Log reader Tool: Demo vs Licensed

The SQL Log viewer is available in two different versions i.e., Demo and Licensed Version. Let us have a look:

  • Demo Version: The demo version of the software is freely available for download and supports to preview of SQL Server Transactions (INSERT, UPDATE, DELETE), preview Login users that has made changes on database tables, transaction Query, Time. But, doesn’t allow to export Transaction details for recovery purpose.
  • Licensed Version: The licensed version of SQL Transaction Log reader tool permit users to preview all the records within the database. Moreover, it can open and read the complete log activity in SQL server database. Also, it allow you to export all tables data along with deleted records direct in SQL Server Database or SQL Server Compatible script or CSV format.

The Demo version of this SQL Log Recovery available for download in SysTools Group Official Website

Preview SQL Logs

What is New Added in SQL Log Reader Tool to Explore LDF

  1. Allows to View Multiple LDF Files: SQL LDF File Viewer permits you to open and read multiple transaction log files activity in single go. For this, you just need to add parent .ldf file of your database, if you are scanning with offline option. The software will scan and preview transaction activities of all other .ldf database files of that database.
  2. Analyze Same record Update
    Multiple Times and then Delete: SQL log file reader allow you to preview the transactional activity of same record (Updated multiple times and then Delete). This will allow users to track all the database changes for a specific record.
  3. Auto Fetch SQL Server Name: While scanning the transaction logs of a specific database with Online DB option,The SQL transaction log reader tool will allow you to click on drop-down button to auto fetch the Server Name. In case, the software doesn’t shows the desired Server Name, then you can enter it manually.

Existing Features of SQL Log Viewer

  1. Preview All Transaction Records: SQL Server log file viewer scans and load all available tables transactional activities, present in SQL database and creates a preview. It will help to view SQL transaction log file with fields such as Transaction, transaction time, Table name and query. Users can view all records with Login Name Authentication for Insert, Delete, Update etc.
  2. Option to Sort LDF File Elements: The sorting option is available within SQL Log viewer to re-arrange the order of the listed items. Moreover, users can sort each element according to their properties such as transaction, transaction name, transaction time, table name, and query.
  3. Scan with Online or Offline Options: The SQL log file viewer offers two different scanning options to read transactional activities of your database. The Online option asks for SQL Server information like: Server Name, Authentication Type. In Offline option, the software will allow you to browse .ldf file along with its associated .mdf file and preview the transaction log activities.
  4. Export Selective Tables: While moving the SQL LDF file queries, you can check or uncheck all tables for saving only selected data. If you want to restore SQL Log file data from the desired table, then, select and export the desired tables direct to SQL Server Database or SQL Server compatible script or CSV format.

Technical Description of SQL Server LDF Reader Tool

Processor Intel® Pentium® 1 GHz processor (x86, x64) or equivalent
Support RAM Around 2GB (gigabyte) of RAM
Hard Disk Storage Space Minimum 100 MB space for installation
Operating System Windows 10 & all below editions

Final Verdict

After considering the overall functionality of the software, we can summarize that SQL Transaction Log Reader is a reliable tool that allows a user to read and view multiple .ldf files. The software is capable enough to view SQL transaction log with fields such as Transaction, transaction time, Table name and query. Regardless, we can say that the SQL log Viewer is a robust software due to its great features.

SQL Server Latest Updates (Jun. Jul. 2018)

Directly from the SQL Server Release Service blog, here the latest updates for SQL Server 2016 SP1, SP2, 2017 RTM and 2014 SP2:

Cumulative Update #12 for SQL Server 2014 SP2

Cumulative Update #10for SQL Server 2016 SP1

Cumulative Update #2 for SQL Server 2016 SP2

Cumulative Update #9 for SQL Server 2017 RTM

…Stay Tuned! 🙂