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 nor 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

Challenge

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 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

Introduction

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

Open LDF file and view LDF file content

Every SQL Server database is mapped over a set of operating-system files. These files store data and log information. Individual files are used only by one database, and data and log information are never mixed in the same file. While data is stored in an MDF file, all transactions and the SQL Server database modifications made by each transaction are stored in an LDF file – a transaction log file which is an essential component of the database. Conceptually, the log file is a string of log records. Physically, the log records are stored in one or the set of physical LDF files that implement the transaction log

September 18, 2013

Tracking DDL changes in SQL Server – the ‘Trouble with Triggers’

Tracking changes is an essential SQL Server security task. Besides data change history, which includes DML operations (e.g. INSERT, UPDATE, and DELETE), tracking DDL changes in SQL Server, changes that affect database objects, is of high importance too. In this regard, various techniques can be used as a schema change auditing solution. One of the most common method are DDL Triggers

August 28, 2013

4 techniques for recovering lost tables due to DROP Table operation

An accidentally dropped table may be recovered in several ways. The choice of technique depends on the resources available for the recovery. The first choice is usually a database backup. But, even if the one doesn’t exist, the dropped table may still be recovered (in some cases even quicker than with a backup).

August 15, 2013

How to recover views, stored procedures, functions, and triggers

Regardless of precautions taken to protect your SQL Server, accidents may still occur, causing serious consequences, such as data and objects loss. We will now analyze two possible ways to recover SQL objects – views, stored procedures, functions, and triggers, lost to accidental DROP statement use.

August 5, 2013

Read a SQL Server transaction log

SQL Server transaction logs contain records describing changes made to a database. They store enough information to recover the database to a specific point in time, to replay or undo a change. But, how to see what’s in them, find a specific transaction, see what has happened and revert the changes such as recovering accidentally deleted records

May 27, 2013

Automating daily transaction log reading

One of the common questions our customers have about auditing is – how to automate transaction log reading and have all the information you might need at hand. Another common request is – how to save the transactions I see in the ApexSQL Log grid, so I can easily filter, group, order and query them without running the whole ApexSQL Log reading process again? In case of large transaction logs, the reading process may take a long time, and I want to avoid running it each time I want to see the transactions

May 20, 2013

Audit SQL Server database security changes

The saying “An ounce of prevention is worth a pound of cure” is ever so true when it comes to SQL Server security. Even if everything seems fine with your SQL Server environment from a security standpoint (i.e. no unexpected slowdowns or increased network traffic; none of the data or the objects are damaged corrupted or missing), as we’ve outlined in several articles before, having an auditing system up and running can be literally a life savior when it comes to any suspicious activities, such as unauthorized permission changes or compromised SQL logins. So, how can one set up SQL Server auditing?

April 26, 2013

Audit SQL Server permission changes to improve overall security

One of the most important tasks for a DBA aiming to keep database and the data in it secure and away from unauthorized access or, heaven forbid, malicious changes is to always stay on top of the effective SQL Server permissions his users have over the SQL instances as well as the databases, database objects and data stored in them. Although this might seem like a pretty straightforward task, as the number of database users grows on one hand, and the number of databases and objects on the other things can get really complicated. Add to that the ever changing business requirements, and soon, unless you have some kind of documenting system in place, you can end up with users not having sufficient permissions or even worse – users having more permissions that they actually require

April 23, 2013

Recover deleted records in Dynamics CRM 2016

Even though Dynamics CRM 2016 is very on clear on the fact that that record deletion is permanent and even provides a warning, it’s not unusual one or more CRM records to be deleted by accident. The bad news is that once the records have been deleted, there is no way to undo the deletion as removing a record from CRM actually translates to deleting it from the underlying SQL database.

That is one of the biggest differences between Dynamics CRM 4 and Dynamics CRM 2016. Unlike Dynamics CRM 2016, Dynamics CRM 4 uses the “soft delete” approach. This means that a deleted record in Dynamics CRM 4 could be recovered relatively effortlessly – to recover a deleted record from Dynamics CRM 4 all you have to do is to connect to the underlying database, locate the deleted record and simply update the value in that record’s IsDeleted column to false. So, how can one go about recovering deleted records from a Dynamics CRM 2016 database?

April 12, 2013

Recover a SQL Server database using an old backup and the current transaction log file

How to recover a SQL Server database using an old backup and the current transaction log file DBAs have various disaster recovery plans. One of the questions they have to answer before setting up a right plan is how much data can they afford to lose. Many of “accidental DBAs” become aware that making regular full database backups might not be enough, only after it’s too late.

April 4, 2013