Spotlight on ApexSQL Log : SQL Server Audit & Recovery Tool

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.
Currently dropped table support on SQL Server 2005
- 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.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |