Recovery matrix
To summarize what we’ve got so far, we can put up the following matrix:
| Scenario: | Recovery Option: | Source: | Quality of Recovered Data: | Completeness of Recovered Data: | Recovery Timeframe: | Recovery Duration: | Database Downtime: |
|---|---|---|---|---|---|---|---|
| Deleted rows | Deleted Data Recovery | – Online Transaction Log – Transaction Log Backup – Detached Transaction Log |
Excellent | Total | Indefinite as long as the transaction log data is kept | Depends on the size of transaction log | No downtime |
| Truncated table | Truncated Data Recovery | Online database | Very good – Excellent Sometimes this option can recover more data than what was actually thought was lost |
None – Total Depends on intermediate database activity |
Probability of successful recovery decreases as time passes | Depends on the size of the database | For best results database should be in read-only mode during recovery process |
| Dropped table | Dropped Data Recovery | – Online Transaction Log – Transaction Log Backup – Detached Transaction Log |
Very good – Excellent Sometimes this option can recover more data than what was actually thought was lost |
None – Total Depends on intermediate database activity |
Probability of successful recovery decreases as time passes | Depends on the size of transaction log and on the size of the database | For best results database should be in read-only mode during recovery process |
| Detached MDF file | Lost Data Recovery | Detached MDF file | Low – Excellent Depends on the amount of corrupted meta-data in MDF file |
None – Total Depends on the amount of corrupted table data in MDF file |
Indefinite as long as the MDF file is kept | Depends on the size of the MDF file | During recovery process |
| Dropped database objects | Dropped Objects Recovery
Fully supported on SQL Server 2000. |
– Online Transaction Log – Transaction Log Backup – Detached Transaction Log |
Excellent | Total | Indefinite as long as the transaction log data is kept | Depends on the size of the transaction log | No downtime |
The Command Line Interface
You can use ApexSQL Log either via the GUI or via the Command Line Interface (CLI). The CLI provides a powerful instrument to leverage the full potential of the software in unattended mode. Typical examples for the use of the CLI are tasks where there is no human supervision possible or necessary or integration into some batch processing. In such cases you would create a job and schedule it to run the ApexSQLLog.exe program. Some time you would control, if this job has produced some output that in turn has to be reapplied to the database. Here are some examples of the command line input (taken from the online help of ApexSQL Log):
- Audit all database’s online logs and export the result to BULK files:
apexsqllog /d:a_database /bulk:bulk.sql bulk_record bulk_detail - Audit all database’s online logs and export the result to an XML file:
apexsqllog /d:a_database /xml:output.xml - Audit transaction log backup of a_database on a_server and export the result to a CSV file:
apexsqllog /S:a_server /d:a_database /B:a_log_backup.bak /csv output.csv - Audit live log for INSERTs and UPDATEs and export SQL script:
apexsqllog /d:a_database /O:an_online_log /sql:output.sql /operations:INS UPD - Audit detached log for DELETEs and generate UNDO script:
apexsqllog /d:a_database /D:a_detached_log.ldf /undo:undo.sql /operations:DEL - Audit online log file for all operations by user a_user made on sysobjects table in 2003 and generate UNDO and REDO scripts:
apexsqllog /d:a_database /O:an_online_log /users:a_user /tables:sysobjects /from:20030101 /to:20040101 /undo:undo2003.sql /redo:redo2003.sql - Audit online log file for all operations in one specific second and generate XML and CSV files:
apexsqllog /d:a_database “/from:20040408 204741” “/to:20040408 204742” /xml:output.xml /csv:output.csv - Audit online log file for all operations and generate a DDL file:
apexsqllog /d:a_database /ddl:output.xml - Audit online log file for all operations on ‘sysobject’ tables having ‘name’ field equal to ‘authors’ and export results to a SQL file:
apexsqllog /d:a_database /sql:output.sql /field:(sysobject,name,authors) - Audit online log file with a mapped dropped table id to a new table name:
apexsqllog /d:a_database /sql:output.sql /mapped_tables:(12345,a_new_table)
Conclusion
If you download the trial version of ApexSQL Log and play around with it in your test environment, you will find, that the software makes it actually very easy and convenient to recover from any of the disasters mentioned above. There are no complicated functionalities that make it hard to find a way into the use of the software. The Recovery Wizard and context menu is typically all you need, which means that it typically takes only a few mouse-clicks to undo mistakenly or maliciously executed commands.
The ApexSQL Log tool is one of these kinds of tools that you ideally will never have to use. But since we don’t live in an ideal world and databases run on hardware that might fail and are accessed by users which might not always be fully aware of what they do, don’t fool yourself that you will never have the need for such tools. You will, sooner or later.
]]>