Every now and then, experienced SQL Server DBAs as well as the SQL Server rookies find themselves in the unpleasant situation when some important data has been changed or lost with the monumental task to solve this in the most efficient way. Regardless of the change source – was it an internal or external user, the intent behind it – unintended mistake or a malicious change, or even the exact nature of the change – update, delete, drop or something else, database administrators are faced with the task to recover the lost data and enable users to continue using the database as if the recovery was never required by providing the data recovery. Let us learn about how to recover lost data using the transaction log files.
Even though data recovery is not always possible, especially in cases of heavy corruption, disk fails or other major calamities, or when users find themselves without any backups or files for the recovery, recovering data lost due to rogue INSERT, UPDATE and DELETE operations, as well as due to ALTER, DROP or even CREATE operations is possible in 100% cases when the appropriate recovery sources (live database or full database backup (prior to the changes) and consequential transaction log backups) are available.
Recover Lost Data
Here are more details on the requirements:
Database is still live on the SQL Server instance (no other issues with the database except that the data has been changed/lost) or the full database backup taken prior to the unwanted changes is available and can be restored on the SQL Server – we will need to connect to the database to perform the recovery of the lost data.
Database is in the full recovery model – this ensures that information on all database changes has been written in the online transaction log file. In case that database is in the simple recovery model, SQL Server will overwrite online transaction log file content on a regular basis, and operation history with recovery information will not be available for recovery. In some cases, though, recovery can be performed even on databases in a simple recovery model (when traffic on the database is really low, and recovery is performed immediately after the unwanted changes, but in this case, recovery is highly unlikely to recover 100% of the lost data, since due to SQL Server nature, information from the online transaction log file will probably be partially deleted
Online transaction log file or all consequential transaction log backups which follow the latest full database backup are available in case if database transaction log backups are being created on (ir)regular basis – they can be stored on any accessible network drive or on the local disk and accessed remotely
With these requirements met, we can use ApexSQL Log in order to completely recover the lost data. ApexSQL Log is a transaction log reader which enables users to examine online transaction log file or transaction log backups of Microsoft SQL Server databases, analyze content of log files in a comprehensive grid, or perform data or structure recovery of the lost data by creating a rollback script. The rollback script will create the opposite operation from the ill-fated one in order to negate it and return the original field value or table structure. The rollback or ‘Undo’ script created by the ApexSQL Log can also be edited by the user to fine-tune the recovery process.
In order to showcase the recovery of lost data with ApexSQL Log, let’s assume the following:
Our SQL Server database is in the full recovery model
Latest full database backup has been created this morning at 08:00am and regular transaction log backups are created every hour
Somewhere around 11:35am, several rogue delete operations have occurred on couple of tables
To start the recovery process:
Start ApexSQL Log and initiate new session by clicking on the ‘New’ button in the main ribbon – this will start a new ‘Session’
The first step of the session wizard is a “Database connection”. Simply choose a SQL Server instance, choose authentication method and provide valid credentials and choose the database which needs to be recovered from the drop-menu
After clicking on the ‘Next’ button, the ‘Data sources’ step of the wizard is shown. In this step, we need to add all relevant consequential transaction log files from the last full database backup onwards (or audit only online transaction log file if backups are not being created). These can be added by clicking on the ‘Add’ button, or by using ‘Add pattern’ option to add multiple files in a bulk. Conveniently, ApexSQL Log automatically adds full and transaction log backups for audited database by default, so the only remaining task is to choose (check) files we need to audit
Note: as shown in the screenshot above, online transaction log has been unchecked, since in our example we are creating transaction log backups, and analyzing online transaction log file will not bring value to our analysis
Click on the ‘Next’ button leads to the ‘Output’ step of the wizard. In case user wants to perform a thorough analysis and investigation on the rogue changes (who made them, when, how…) the choice to ‘Open results in grid’ is the best option – auditing results are shown in comprehensive grid which is suitable for investigation. Other options (“Create before-after report” and “Export results”) allow users to create specific or overall auditing reports. Since in the goal in the example above is to simply and efficiently recover the data, option “Undo/Redo” should be chosen
After selecting the most appropriate option, the wizard leads to the ‘Filter setup’ step which allows users to use various filters to fine-tune the auditing results. For our example, lets setup a date/time filter to audit changes only between 11am and noon
Uncheck all operations except deletes
And choose the two tables that were affected by the deletes we want to roll-back. Other filters can be used in addition to the above mentioned ones to further focus the auditing
Click on the ‘Next’ button forwards user to the final step of the wizard. The ‘Undo’ script is already selected by default, so click on the ‘Finish’ button will complete the wizard.
Once the processing is completed, ApexSQL Log will display auditing results (statistics) and creates an undo script to rollback unwanted changes.
Created “undo” script can be opened, edited and executed directly in the ApexSQL Log internal editor, or via any other editor tool such is SQL Server Management Studio
As shown in the Auditing statistics, 23 rogue deletes have been found, which means that the rollback script that was created contains undo operations for all 23 deletes. Click on the ‘undo.sql’ link will open the script in the internal editor. The script can now be inspected or edited if the need requires it
The only task that remains at this point is to execute the script against our database. To do this, click on the ‘Connect’ button and choose our database and provide connection credentials. Once this is done, click on the ‘Execute’ button and the script will be executed and data recovered – ApexSQL Log will show the execution results in the ‘Message’ pane
This concludes the recovery process and roll-backs all of the rogue deletes that have occurred on the observed two tables – data is inserted back to the tables in its original state as it was before the unwanted deletes have occurred.
As mentioned above, the same approach can be used to perform a recovery from other DML and DDL operations which were unintended. User simply needs to adjust the filters in the session wizard to include/exclude only those operations or tables or users which are relevant for the recovery process.
Alternate approach to the recovery is to ‘Open results in grid’ when choosing auditing ‘Output’ and completing the remaining steps in the same manner as above. The results will be first shown in the comprehensive grid, where users get another chance to look at the results and to further filter the audited operations in addition to the various details which are shown for each operation (including time of occurrence, SQL Server login that ran the operation, LSN…). Once the user is satisfied with grid filtering, the recovery script can also be created directly from the application main ribbon.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
First appeared on Recover Lost Data Using the Transaction Log Files