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:Connect to the live database from which the records have been deleted
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:
Use the ""Time range"" section in Filter setup options step to narrow down transactions to the ones that happened within a specified
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
If possible, specify the tables from which the records have been deleted. If not, leave all user tables selected
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
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
Instead of ""Open results in grid"", click ""Undo/Redo""
Select ""Undo (Rollback) script"" and specify the file path and name
The undo script that inserts all deleted records back in their tables will be created
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
"© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center