How to recover deleted SQL data from transaction logs

In the Recover deleted SQL data from a backup or from online database files, we showed two recovery scenarios, the one from a full database backup and the other from the online database data file – the MDF file. Besides these, there is one more resource you can use when it comes to SQL data recovery – a transaction log

Every schema and data change in a SQL Server database is added into an online transaction log as a log record. If the delete transactions you want to reverse are in an online transaction log, detached transaction log or transaction log backup you can read them and undo the deletes

If your database is in the:

• Full recovery model, the delete transactions are still in an online transaction log or a transaction log backup, providing that they have been created after the deletion of records
• Bulk_logged recovery model, the delete transactions are fully logged in an online transaction log except for the deleted BLOB data. As BLOB deletes are not logged in the online transaction log, and the BLOB inserts were not logged due to the Bulk_logged recovery model, their recovery will not be possible. For other data types, the delete transactions are in an online transaction log or a transaction log backup created after the deletion
• Simple recovery model, and there have been many transactions executed against the database after the deletion of records, the delete transactions might not be in the online transaction log anymore. In this case, the sooner you run a SQL Server online transaction log reader against the database, the better the chances for successful recovery are

Use ApexSQL Log

To read a database online transaction log, detached transaction logs and transaction log backups, both native and natively compressed, use ApexSQL Log. It’s a SQL Server transaction log reader that can audit, revert and replay data and object changes including those that may have occurred before ApexSQL Log was installed. It also captures information on the user, application and host used to make each change

1. Start ApexSQL Log

2. Connect to the live database from which the records have been deleted

   

3. If any transaction log backups are created after the records have been deleted, “Add” them all in the next step. Note that the transaction log backups must form a full chain if the records you are trying to recover contain BLOB data (see below for more details on this)

4. In case you have a detached transaction log that contains deleted records (for example, if you took the database offline and copied the LDF file immediately after the deletion), you can use it as well:

   

5. Use the “Time range” section in Filter setup options step to narrow down transactions to the ones that happened within a specified

   

6. Use the “Operations” filter to narrow down the results only to the deleted records. To do that, uncheck all schema operations (DDL), as well as Insert and Update in the Data operations (DML) list

   

7. If possible, specify the tables from which the records have been deleted. If not, leave all user tables selected

   

8. Click “Finish”

9. All DELETE transactions are shown in the main grid. All you have to do is select them all or just the ones you need, and click “Undo” in the menu to create an undo script for them

   

10. To create an undo script for all the records shown in the grid, click Undo in the menu. To create an undo script for only some of the records, select them in the grid, and in the grid context menu select Undo

    The script that inserts the deleted records will be created

    

11. To execute the script, click in the menu

Use ApexSQL Log to create an undo script immediately

The described method with the results shown in the grid is acceptable only for a small number of records. When it comes to millions of deleted records, we recommend creating an undo script immediately, instead of showing the results in the grid first. The steps 1 to 7 are the same as above

8. Instead of “Open results in grid”, click “Undo/Redo”

   

9. Select “Undo (Rollback) script” and specify the file path and name

   

10. Click “Finish”

    The undo script that inserts all deleted records back in their tables will be created

    

Use ApexSQL Recover

This is another ApexSQL tool that can read SQL Server transaction logs – an online transaction log, detached transaction logs and transaction log backups both native and natively compressed. ApexSQL Recover is a recovery tool for SQL Server databases which recovers deleted, dropped or truncated data. It recovers objects and data lost due to drop operations and restores both deleted and online BLOBs as files. The recovery process is quite similar to the one described here: Recover deleted SQL data from a backup or from online database files – Use ApexSQL Recover. While ApexSQL Recover can be used in the exact same manner as ApexSQL Log to recover the deleted data, since the tools share the ability to read from transaction log files and create rollback scripts, ApexSQL Recover has another mechanism which can be used for recovery described below:

1. Start “ApexSQL Recover”
2. Select “Recover lost data due to a DELETE operation”
3. Specify the SQL Server instance, credentials and database where the records have been deleted
4. Select “Several sources are available for the recovery”
5. The online transaction log, all transaction log backups and database backups created for the selected database are automatically listed. To add additional transaction log backups, or a detached transaction log that contains the deleted records, use the “Add” button. Note that the transaction log backups must form a full chain
6. Specify the time when the delete statement was executed. If you don’t know when this happened, select “Date and time are unknown”
7. Use the table filter to narrow down the recovery to just specific tables, if applicable
8. Click “Recover”
9. A script that inserts the deleted data is created. Check it out and execute

The good news for deleted records recovery is that there is a chance of successful recovery even without a database backup, or a transaction log backup chain. Note that successful recovery of deleted data with BLOBs is not possible without a full chain of transaction logs. As the delete BLOB transaction doesn’t log the actual deleted value, it’s necessary to go through the sequence of transaction log backups all the way to the beginning of the chain. If the chain is broken, the reconstruction might be incorrect. For example, if the info about a BLOB data update is missing, the originally inserted BLOB will be recovered instead of the deleted one

Even with a database in the Simple recovery model, there is a chance of recovery. In the Simple recovery model, new transactions logged in an online transaction log overwrite the old ones. If the online transaction log is for example 100 GB, and you have 1 GB of transaction log data logged every day, the online transaction log will be completely overwritten in 100 days. That means that you might recover the data deleted up to 100 days ago

Of course, this by no means implies that you should rely on this and forget about your SQL Server disaster recovery strategy

ApexSQL has two tools that can help with transaction logs. ApexSQL Log provides more filtering options and granularity of recovered records – besides filtering by time and table name which is a common feature for both tools, it provides filtering system tables, records based on a certain column value, transaction description by a user who committed the deletes, and in case when the ApexSQL Log Connection monitor was running at the time the transactions occurred – by the application and host name

ApexSQL Recover has less filtering options, but provides more recovery output options. Besides creating a T-SQL script that inserts records, it can also create a new database and recover deleted records directly into it