Whenever an accidental or malicious data loss occurs, it is important to take the most efficient approach in order to perform the most appropriate recovery process and recover full range of lost data. Also, it is important to choose the right tool which will provide the highest possible chance for the successful recovery.
This article will provide details on how to ensure that recovery chance is maximized, by taking the correct post-incident steps, understanding the recovery process, and making the right choice between ApexSQL Log and ApexSQL Recover when planning the recovery.
How to minimize the chance of the data being overwritten or lost beyond recovery, and maximize recovery success rate
When a data loss occurs, the information on the data loss is usually contained within MDF and LDF files. With this in mind, it is of utmost importance to protect these files as soon as possible after the incident is detected. Since SQL Server overwrites data in the MDF file, and also overwrites the LDF file when database is in the simple recovery model, it is crucial to make copies of these files in order to prevent overwriting of the information in MDF and LDF files that is required for the data recovery.
In order to create copies of MDF and LDF files, the database needs to be taken offline first, so that the copies of MDF and LDF files can be created (and taken back online after copies are made). Another option to protect the information is to take database to read-only state, immediately after the accident. This article provides more details on the ‘What to do immediately after the accidental data loss’ topic.
Another important thing to keep in mind is the fact that restoring a backup should be avoided at all cost, for the time being. If a database backup is restored, all data and structure changes that have occurred after that backup will be gone beyond recovery, since restoring the database will overwrite information in the MDF and LDF files which is used for the recovery reconstruction. Due to this fact, backup restore job should be avoided in order to perform recovery with ApexSQL Log or ApexSQL Recover, which should allow user to recover full range of lost data.
Database recovery model
In many cases, successful recovery is directly dependent on the database recovery model. If the database is in the simple recovery model, the transaction log file (LDF) is overwritten on a regular basis, which means that the information used to perform the recovery can already be lost in this case.
The simplest way to check the database recovery model is through the SQL Server Management Studio:
- Connect you the SQL Server instance
- In Object Explorer right click on the database, and select Properties
-
In the Database Properties dialog, select the Options page, and check the Recovery model for the selected database
- ApexSQL Recover – the tool will read the MDF in an effort to read the ‘original’ data and recover it. More details on the recovery from DELETE operation with ApexSQL Recover can be found in this article
- ApexSQL Log – the tool will read transaction log file(s) and search for all DELETE operations. After the analysis, user needs to select all unwanted DELETE operations and the tool will create an undo script which will revert all these changes back to the original state before the DELETE operation was executed. Here is a complete guide on how to perform recovery from DELETE operation with ApexSQL Log.
- Basic information on the windows and SQL Server versions
e.g. We are using Windows 7 Ultimate SP1 x64 with local SQL Server 2014 instance (ver 12.0.2269) - Detailed information on the issue and on the available resources
e.g. We’ve lost the data 2 days ago due to accidental TRUNCATE operation, and we perform daily transaction log backups, and weekly full database backups – all are available for the recovery - Details on the issue you encounter or any questions regarding the process or tools performance. Screenshots would be greatly appreciated where applicable.
e.g. We have created a recovery script with ApexSQL Recover, but are unable to execute it from application or SSMS. What are our options, and what are the best practices you would recommend? - If there are any issues with the application, or the process itself, it would be highly beneficial to send application log files to support team for analysis. These files contain information on the application processes, and no sensitive data regarding database under recover is contained within. These can be sent automatically by clicking on the Send logs button in the Resources tab in the main ribbon.
If the database recovery model is full, the chances for successful and complete recovery are high. If the recovery model is set to simple, the recovery process may be limited and recovery range can suffer a heavy blow. The ‘Recovery possibilities when a database is in simple recovery mode’ article offers detailed explanation on what to expect, and how to perform recovery in case that the database recovery model is set to simple.
Choosing the best tool for the job
With the combination of ApexSQL Log and ApexSQL Recover, it is possible to achieve complete recovery of data lost due to unwanted or malicious changes occurred due to: DELETE, DROP, TRUNCATE and UPDATE operations.
Recovery from DELETE operation
When unintentional or malicious DELETE operation has been executed over some tables in the database, there are two recovery options:
Recovery from UPDATE operation
ApexSQL Log is the tool that is used to revert any unwanted updates back to the original state. Similar to reverting unwanted changes which have occurred due to unwanted DELETE operations, ApexSQL Log can revert back UPDATE operations by creating a reroll (undo) script to recover original state of affected rows/values. Here is a complete guide on how to recover from UPDATE operations.
Recovery from DROP operation
The most suitable tool for the job, with the best recovery results in case of DROP operation recovery is ApexSQL Recover. The tool can recover both structure and data lost which has occurred due to the unwanted, malicious or accidental DROP operation. Here is how.
Another alternative to recover from DROP operation is to use ApexSQL Log. However, ApexSQL Log can recover only structure lost due to DROP operation, here is how. Data recovery is beyond ApexSQL Log when recovering from DROP operation.
Recover data lost due to a TRUNCATE operation
When TRUNCATE table operation is executed, all rows from the targeted table are removed. In order to recover those rows, ApexSQL Recover should be used in the following way to ensure its full potential has been utilized, and the chance to recover truncated rows is maximized.
For more details on specific recovery scenarios, the Recovery flow chart article offers variety of details and helpful advice to users so they can choose the most suitable tool for their current recovery scenario. If you have further questions, you can email us at support@apexsql.com with details on your data loss scenario and we can recommend a tool and an appropriate process to ensure highest recovery chance.
In some cases, ApexSQL Recover will create a large recovery script which needs to be executed to complete the recovery process. These very large scripts cannot be executed from ApexSQL Recover directly, or from SQL Server Management Studio. To execute this kind of scripts, one can use osql, sqlcmd or ApexSQL Run Script utility and here is how.
Optimizing results
Another aspect of recovery processes is to check and ensure that all of the missing/lost data has been recovered. In some cases, it is necessary to add additional sources of information to increase recovery results, such as database backups, or transaction log files/backup. Also, providing a full chain of transaction log backups is necessary to ensure a 100% recovery in some cases.
In order to optimize data recovery results with ApexSQL Recover, it is important to take specific actions described in this article.
When recovery is being performed with ApexSQL Log and the number of transactions audited from the transaction log file is less than expected, it is important to provide appropriate transaction log files, and to use various filters to ensure all relevant transactions are shown – here is how.
FAQs
Q: Can I get a full version without the trial limitation so I can ensure the recovery will be successful?
A: No. Our trial functionality should be sufficient in most cases to allow potential customers to determine if the tool won’t recover any data at all or if it will most likely recover some/most/all.
Q: Can I get help from ApexSQL in managing my recovery?
A: Our support engineers can help you utilize the software, answer any questions, and assist with any problems you may have up to including conducting WebEx sessions. But we won’t be able to manage your recovery directly for you. For this you’ll need to understand the toolset and work with SQL Server professionals to assist in the recovery. If they have questions, etc we can help.
What to do if the above information wasn’t sufficient for a specific case?
The above information should address majority of user recovery cases. Nonetheless, from time to time, a specific case appears when experts help is required in order to ensure that the recovery process has been performed in the best possible way, and that all available resources have been used to the maximal limits. ApexSQL has several highly qualified support engineers that are available to all users that are trialing our products, as well as to all customers under subscription.
The most efficient way to get the support engineers help is to contact them directly via mail to the following address: support@apexsql.com
Support engineers will work to the best of their abilities to quickly solve the issue via mail. If the need requires it, customers can be contacted via webex meeting to directly discuss the potential issue via call, or provide assistance via share-screen available in the webex.
When contacting support team, it is suggested to provide the following details, to ensure that help is received as soon as possible, and the issue is efficiently solved:
Or, they can be manually zipped and attached to the email. Application log files are located in the following locations:
For ApexSQL Log
c:\Users\Your_User\AppData\Local\ApexSQL\ApexSQLLog\LOG\
For ApexSQL Recover
c:\Users\Your_User\AppData\Local\ApexSQL\ApexSQLRecover\LOG\
