It is not that uncommon to find yourself in a situation when some inadvertent delete operations have deleted important data from a SQL Server database. The highest priority in these situations is to discover what exactly was deleted and how to get the deleted data back as quickly as possible. In this article, we are going to show you how to quickly recover data lost due to inadvertent delete operation using ApexSQL Log and ApexSQL Recover tools. Even though both tools generate the same output, a recovery script which rollbacks unintended deletes, the mechanisms used by them to acquire recovery information and create rollback script are quite different, so if the recovery with one tool doesn’t help, the other tool may.
In order to start the recovery process, let’s assume that our database has already been affected by some inadvertent delete operations. Also, the database is in the full recovery model which ensures that the transaction log information contains full information on transactions that have occurred on the database which is used as a source for the recovery.
Recovery with ApexSQL Log
ApexSQL Log is a Microsoft SQL Server database transaction log reader which allows users to view the data contained within the transaction log files and to roll-back any changes found in the transaction log files.
To get the process going, start ApexSQL Log and then provide the database connection details:
- Select the SQL Server instance
- Choose the authentication method and provide valid credentials
- Choose a database on which the recovery will be performed
Once the database connection details have been provided, click on the “Next” button to proceed.
In the next step of the wizard, the Data sources, ApexSQL Log automatically adds the online transaction log file as a source for auditing. In the case when a user is creating regular transaction log backups or a transaction log backup has been created since the inadvertent delete, these should also be added for analysis. When adding a transaction log files for analysis, make sure to add the full chain of transaction log backups to ensure all the information for auditing/recovery is available to ApexSQL Log. Once all appropriate transaction log files have been added and checked in the “Data sources” step of the wizard, click “Next” to proceed.
The next step of the wizard requires the user to choose an output type. For the recovery of deleted data, there are two options. First, the user can “Open results in grid” to display them in a comprehensive grid for in-debt analysis before performing actual recovery. The second option allows the user to immediately perform the recovery. Both options are viable, but for the purpose of this solution, let’s choose the Undo/Redo output.
The Filter step of the wizard allows users to utilize various filters to narrow down and fine-tune the information that is being extracted from the transaction log files. To achieve adequate precision, make sure to use the date/time filter and to focus auditing only for a specific period when an inadvertent delete has occurred and choose to only undo delete operations in this period. Other filters can be used to further fine-tune the auditing, including date-time, operation, table, transaction and user filters, as well as some more advanced options. Click “Next” to proceed.
In the final step of the wizard, the only remaining job is to ensure that the Undo script is selected as an output. Conveniently, ApexSQL Log creates a Command Line Interface script as per provided input which can be executed, if the job ever needed to be run repeatedly. Click on the Finish to complete the process.
After a few moments, ApexSQL Log creates an undo script in the designated location and shows auditing results and statistics.
At this point, we can examine the created undo script by clicking on the “Open file” link. This will bring up the ApexSQL Log internal script editor where the user can check the rollback (Undo) script and make any changes if necessary, and finally Execute the undo script to complete the recovery process. Once the script is executed, the inadvertent deletes will be completely rolled back, as if they never happened in the first place.
Recovery with ApexSQL Recover
As we’ve mentioned above, besides performing recovery from inadvertent deletes with ApexSQL Log, there is another solution, and that is ApexSQL Recover. ApexSQL Recover is a SQL Server recovery tool which reads the deleted transactions from the database MDF file. When a record is deleted, this information will exist in the MDF file for some time, until it gets overwritten. Due to this fact, it is recommended to immediately take the database offline and create a copy of MDF file as soon as possible after unintended deletes occur in the database to ensure the highest possible chance of successful recovery.
Since ApexSQL Recover shares a feature with ApexSQL Log which allows it to read transaction log files and create recovery scripts (Undo/Redo), it can be used in the exact same manner as ApexSQL Log is used in the solution previously described in this article. To start the process, simply run ApexSQL Recover and choose “Transaction log data” option in the main ribbon and follow the exact steps, same as with ApexSQL Log as shown above.
In addition to this approach, ApexSQL Recover has another mechanism for the recovery of deleted data. To use this second approach, first choose the ‘Recover from delete operation’ option in the main ribbon. In the next step, select the SQL Server, provide connection credentials and choose a database on which the recovery will be performed, same as with ApexSQL Log before.
The next step of the wizard offers a couple of choices, based on the sources we are using for the recovery. The bottom option should be selected If there are no additional transaction log files. The middle option allows users to include additional transaction log backups as sources for recovery and the “Help me decide” option leads the user through several steps to help decide on the best choice if they are not sure on what resources they have available. For this example, let’s choose the option with no additional transaction log files since we do not have any in our example.
In the next step of the wizard specify the time range where the inadvertent deletes have occurred and proceed to the next step. In this step, the user can fine-tune the recovery by including or excluding specific tables from the recovery process.
Finally, the choice between creating a recovery script, or recovering directly to a new database brings the wizard to an end. If the “Recover to a new database” option is selected, the user will need to specify SQL Server connection details, and in the case of the “Save recovery script to a file”, a script will be generated, in the same way as it was with the ApexSQL Log.
To conclude our example, let’s choose the “Save recovery script to a file” option and click “Next”. After a couple of moments, ApexSQL Recover will create a recovery script and display recovery information. The script can be inspected, edited and executed through the internal script editor (the same one we’ve seen in ApexSQL Log before) by clicking on the ‘View script’ button.
October 11, 2016
