SQL Server database recovery flow chart

There are numerous issues that can bring up to the data loss scenarios. The first thing every good DBA needs to do is to ensure that a post-disaster period has been handled with care. In order to ensure that the data can be recovered, or malicious changes reverted, it is strongly advisable to take the following steps immediately after an incident:

1. Detach a database
2. Create copies of the database MDF and LDF files
3. Move the MDF and LDF copies to another server, and attach them
4. Copy any existing database or transaction log backups to the other server

After the post-incident precautions have been handled, the most appropriate recovery scenario must be chosen, in order to ensure highest rate of recovery success.

This article is intended to help users choose the best recovery solution for their particular disaster recovery scenario when using ApexSQL Recover, a SQL Server recovery tool capable of recovering deleted, dropped or truncated data and objects, roll back unwanted changes or even extract table structure and data directly from the database backups. Additionally, ApexSQL Recover can recover deleted BLOBs or even extract them from the live databases as blob files.

Here is a flow chart intended to help any user to choose the most suitable approach for their respective recovery scenario:

Additional details on the specific recovery scenarios which may result in partial recovery:

1. Connection to a live database is not available, yet full database backup exists

   In this case, the Extract from database backup feature should be used for maximum results. This option enables the user to extract the data and/or structure only for the specific tables, instead of having to restore a full database backup on another location, and to extract from there, saving both time and space. Recovered data can be added to the new database or in the shape of the SQL Script for easier recovery process. Full step-by-step guide is available in this article.

2. Recovering from DELETE or DROP operations when database is not in the full recovery model

   ApexSQL Recover’s full functionality is available only when a database is in the full recovery model. Since the simple recovery model doesn’t guarantee that the information which ApexSQL Recover needs will be available, due to the fact that there are no transaction log entries since SQL Server overwrites information in the ldf to keep the disk space consumption at the minimum.

   This leaves only the data in the MDF file as a source of information, and this data is also regularly overwritten by the SQL Server, and is hence not reliable and may already be gone. With all this in mind, recovery range is limited when working with databases with the simple recovery model.One thing to keep in mind is that it is essential switch the database into the offline mode immediately once the need for recovery is acknowledged in order to try and keep the recover source information in both ldf and mdf as complete as possible.

3. Recovering from DROP or DELETE operations when a transaction log has been truncated, and no backups are available

   In case when a recovery from DELETE or DROP table operations is performed, ApexSQL Recover uses the data from the transaction log files (online, backup or detached) in order to perform a recovery. If the transaction log files are not available, the recovery can still be performed, since ApexSQL Recover will check if this data still exists in a database MDF file, since the SQL Server deletes data logically, immediately marking data as deleted, when in reality the data will be overwritten in the MDF file on the first appropriate occasion which can be after some hours, or even days in some cases. This fact makes post-incident actions described at the beginning extremely important and imperative to ensure decent recovery chances.

4. Recovering from the UPDATE, INSERT, ALTER, CREATE, or DROP object operations when a database is in the simple recovery model

   In order for ApexSQL Recover (and ApexSQL Log as well) to show/recover all transactions for a specific database, the recovery model of the database has to be set on ‘full’. In case of recovery model being set to simple, the transaction log files will contain only a limited amount of data, and only a small chunk of the most recent operations will be available for auditing/recovery.

5. Auditing or recovering an incomplete chain of the transaction log files

   When an online transaction log file has been truncated, in order to be able to expect the full range of results, it is essential to provide a full chain of transaction log backups, so ApexSQL Recover (and ApexSQL Log as well) can create a historical recovery, and trace the history of the rows. This is especially important when recovering the UPDATE operations since each UPDATE has at least an INSERT statement as its predecessor, and data history can vary. If the full chain of transaction log backups is not available, the auditing/recovery results may be incomplete, with the UPDATE operations missing full details. Additionally, recovery process will have a decreased performance since the tools will have to perform an additional processing in an effort to create a full history when the transaction log chain is broken.