Continuous SQL Server database auditing and reporting using the transaction log

SQL Server database transaction log files are continuously pumped with the transactional information and details on database changes by the SQL Server itself. Even though the information from the transaction log files and backups can be used as a solid resource for database auditing, SQL Server does not provide a solid solution to utilize transaction log files to their full potential neither does it offer any simple way to explore those transaction log files or analyze the information within in order to perform continuous SQL Server database auditing.

November 10, 2016

SQL Server disaster recovery – How to quickly recover data lost due to an Inadvertent delete operation

It is not that uncommon to find yourself in a situation when some inadvertent delete operations have deleted important data from a SQL Server database. The highest priority in these situations is to discover what exactly was deleted and how to get the deleted data back as quickly as possible. In this article, we are going to show you how to quickly recover data lost due to inadvertent delete operation using ApexSQL Log and ApexSQL Recover tools. Even though both tools generate the same output, a recovery script which rollbacks unintended deletes, the mechanisms used by them to acquire recovery information and create rollback script are quite different, so if the recovery with one tool doesn’t help, the other tool may.

October 11, 2016

Recover a SQL Server database using only a transaction log file (.ldf) and old backup files

Having a good recovery plan that ensures database backups are being taken on a regular basis and safely stored on specific or multiple storage drives greatly increases database safety and recovery options when a disaster or accident occurs. Nevertheless, creating regular full database backups doesn’t provide full point-in-time recovery, since restoring the latest full backup will restore a database to a proper state at the time a backup was created, but all changes, both schema and data, which have occurred after the last full backup will be practically lost.

April 5, 2016

Continuous auditing of SQL Server database using the transaction log


Imagine if you could decrypt and pump all the wealth of transactional information and details contained in the SQL Server transaction log, in human readable format, and then store this as data directly in SQL Server tables, ensuring it wasn’t lost when the log was truncated, and such that data could be queried, reported on etc. using T-SQL.

March 7, 2016

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 1

The PCI DSS (Payment Card Industry Data Security Standard) is a multidimensional security standard designed as a set of technical and operational requirements to protect data of credit card holders. The PCI DSS applies to all entities that store, process or transmit cardholder data, including software developers of applications and devices manufacturers when used in those transactions

This standard originated in 2005 and was created by the PCI SSC (Payment Card Industry Security Standards Council) organization. The PCI SSC organization is founded by American Express, Discover Financial Services, JCB International, MasterCard Worldwide, and Visa, Inc., with a goal to improve security of payment account data via the PCI Security Standards

December 11, 2015

How to continuously pump transaction log file audit data directly to a SQL Server database

In Automating daily transaction log reading, we’ve shown how to automate the process of pumping transaction log data into SQL Server tables with ApexSQL Log. The proposed solution revolved around the creation of a batch file which runs ApexSQL Log CLI commands. The batch file is then scheduled with a Windows scheduler to run on a daily basis. The result is a regular daily update of 2 tables, specifically created by ApexSQL Log to hold audited data, which are populated with fresh auditing results each night.

December 9, 2015

Synchronizing databases using the transaction log – SQL Server Log shipping

No matter how well managed your systems are, accidents may still occur, and potentially lead to disastrous consequences. In order to ensure that there is a disaster recovery solution available, it is always good to have a standby copy of a primary database on another SQL Server instance.

The first way to achieve this is to utilize the SQL Server Log shipping.

March 4, 2015

Reverting your SQL Server database back to a specific point in time


There are certain circumstances in which you may need to roll back your database to a specific point in time. There may be various reasons why this could be necessary but it is usually related to the execution of accidental or malicious DML or DDL statements. For example:

November 20, 2013

How to audit your auditing in SQL Server – tracking when triggers are disabled

SQL Server auditing triggers are mostly used to maintain the integrity of the information on a database, or to provide an auditing trail of data changes. A trigger is a special type of a database object which is automatically executed upon certain conditions – e.g. actions performed by the user. What auditing triggers must provide while auditing data changes are answers to the following forensic questions:

November 6, 2013