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 2011

Even though Dynamics CRM 2011 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 2011. Unlike Dynamics CRM 2011, 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 2011 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

Audit SQL Server database schema changes

Keeping track of the changes made to your database objects is a key part of any SQL database security strategy or compliance policy including, among others, the Health Insurance Portability and Accountability Act, Sarbanes-Oxley, Payment Card Industry Data Security Standard or the European Union Data Protection Directive. However, even if your IT environment doesn’t have to comply to stringent security rules, being able to identify what database object has been changed, who has changed it as well as the exact time of the change, is invaluable in troubleshooting any schema related problems down the road, such as broken dependencies. So, how does one audit SQL Server schema changes?

April 4, 2013

How to recover data that is missing or damaged as a result of a SQL injection attack

What is a SQL injection?

A SQL injection attack is an attack in which a code that attacks the database is inserted into a web site. It’s one of the most common types of web application security vulnerabilities. So it’s better to be prepared, or at least well informed
SQL injections can have an immediate (first-order attacks) and delayed effect (second-order attacks)

April 4, 2013

Checklist for suspicious activity on SQL Server

It seems something went awry with the SQL Server. It’s sluggish, behaves erratically, produces heavy network traffic, there is a significant increase in the server processor or memory utilization, and to top it all there are reports of or database objects and data being damaged or missing.

Where to start looking?

April 4, 2013

How to recover SQL Server data from accidental UPDATE and DELETE operations

The SQL DBA’s worst nightmare is the loss of data due to their own mistake. It’s not uncommon for someone to improperly execute or even forget a WHERE clause entirely when executing DELETE or UPDATE queries, resulting in potentially millions of rows of compromised data

So, which SQL Server data recovery options are available?

April 4, 2013