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. The tools which offer solution are ApexSQL Log and ApexSQL Recover.

ApexSQL Log is a transaction log reader which enables the user to audit data from the transaction log files, and reverts any unwanted changes back to their original state by creating an undo script with the opposite operations (e.g. if an operation has been deleted, ApexSQL Log will revert it by creating a script which INSERTs the data back).

ApexSQL Recover is another recovery tool for SQL Server databases which recovers deleted and truncated data. In addition, ApexSQL Recover can recover data and/or structure lost due to the DROP operations, deleted BLOBs, or extract the specific tables directly from the database backups.

Here is a flow chart intended to help any user to choose the most suitable tool 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, ApexSQL Recover the Extract from database backup feature can be used. 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. 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.

    This leaves only the data in the MDF file as a source of information, and this data is 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.

  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. This fact makes post-incident actions described at the beginning extremely important.

  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 Log or ApexSQL Recover 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 Log or ApexSQL Recover can create a historical recovery, and trace the history of the rows. This is especially important when auditing/recovering the UPDATE operations. 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, auditing/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.

July 29, 2015