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

Verifying SQL database backups automatically

Backups are the starting point for any serious disaster recovery strategy. Creating SQL database backups on a regular basis is just the first step. Equally important is to make sure they are also reliable and restorable. This is the only way to avoid unpleasant surprises in case of a disaster

August 2, 2013

Multiple SQL Server data recovery options

In the past two weeks we had some interesting Solution center articles describing Microsoft SQL Server topics – each described as the problem (or the challenge) users may encounter with SQL Server data recovery

Any of recovery options using Microsoft’s SQL Server Management Studio requires the availability of a full database backup. And, the backup must be restored in order to be used as a recovery data source

July 5, 2013

SQL Server database auditing techniques

SQL Server database auditing is not used only to address auditing requirements for compliance. It has become necessary for the analysis of database actions, troubleshooting problems, investigating the suspicious and malicious activity. It can also help preventing users from inappropriate actions – as if you had a CCTV system on your databases

There are several SQL Server auditing techniques:

June 28, 2013

Auditing triggers in SQL Server databases

One of the essential SQL Server security topics is finding out who did something, what and when. Being able to provide a history of data for various auditing purposes, some of which are enforced by US laws through compliance regulations, is a serious task for any DBA. Since business policies or the compliance regulations require the auditing of data changes within a database, various techniques adapting to the requirements are used to perform database auditing. The common one is using auditing triggers in SQL Server databases.

June 26, 2013

Restore a database to a point in time – part 1

In various scenarios, a SQL Server restore a database to a point in time might be required. Typically, the following situations require this type of recovery:

  • A data corruption occurred by a user malicious action. For example, a user or DBA might delete or update the contents of one or more tables by mistake, drop database objects that are still required during an update to an application, or perform a large batch update process that fails
  • A database upgrading process goes awry or a database upgrade script fails
  • A complete database recovery after a media failure cannot be performed, because the required transaction log and database backups are missing
June 20, 2013

Capturing who saw what in SQL Server

It’s important sometimes to know and have evidence of who-saw-what on specific SQL Server instances and databases. This can be a requirement you have to provide (e.g. to comply with HIPAA regulations) and there are several ways to ensure the capture of that information

In order to capture who-saw-what in SQL Server, it is important to know what was executed on the server. Even though ApexSQL Log (a transaction log reader) and ApexSQL Trigger (trigger-based auditing tool) provide partial information, in order to get the full details on who saw what, ApexSQL Audit is the perfect tool for the job.

June 11, 2013

Recover deleted SQL data from a backup or from online database files

Recovering DELETED records is something we would all like to avoid, but no matter how careful we are, it can come to that. Having regular full database and transaction log backups is a necessary practice and a great advantage in this situation, but even then, bringing back the deleted records might not be easy

June 4, 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

SQL code refactoring – ways to improve database design

A “one-to-many” relationship is one of the most common relationship types as many real world scenarios can be represented using it. For instance, the same product can be sold by more than one supplier; a customer can have more than address, and so on.

May 21, 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

How to compare and synchronize databases programmatically

ApexSQL Diff and ApexSQL Data Diff provide a user friendly GUI for SQL comparison and synchronization of database schemas and database objects. They both include the command line interface which provides the exact same options, and which you can use to schedule unattended comparisons and synchronizations

But, what can be done when the features that the GUI and CLI provide are simply not enough? In such cases a more flexible solution is needed and the good news is – there’s a programmable API

April 30, 2013

How to make complex SQL database deployments easier

Application deployment is not an easy task. For more complex updates, besides a new version of a database that should be deployed, a new version of the application should also be deployed, and even the environment configuration changes (e.g. IIS settings for a web application or some other server settings) and post-deployment testing might be necessary.

Problems with complex deployments

A complex deployment often involves a lot of manual work, which makes it slow and error prone. While these characteristics are not a big problem in a test or development environment, they are certainly unacceptable in production.

April 30, 2013

How to synchronize large SQL Server databases

When synchronizing large databases that contain millions of records, you will come across several challenges The first challenge is to just compare such databases. When the database tables contain millions of records, their comparison through the ApexSQL Data Diff graphical interface will be very slow and in such cases, it is strongly recommend using the ApexSQL Data Diff command line interface. As there’s no need to show millions of records visually in the tool’s grid, such large databases are processed faster.

April 29, 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

Improve the performance of an ETL process

Due to the sheer volume of data usually involved in an Extract – Transform – Load (ETL) process, performance is positioned very high on the list of requirements which need to be met in order for the process to go as smoothly as possible. Here are some guidelines which will help you speed up your high volume ETL processes

April 23, 2013

Synchronize SQL Server databases in different remote sources

The scenarios

The applications used by travelling sales representatives, or other field workers – delivery drivers, visiting nurses, etc., are designed to collect data from remote locations and then send it to a data center. Also, the data from the data center, occasionally need to be sent back to these remote locations, to keep them up to date.

For example, whenever nurses pay a visit to a patient, they enter the information about the visit into the database on mobile devices. At the end of the day, all these entries created during the day are sent to the central database in a hospital. After that, the nurses can synchronize mobile devices with the database in the hospital data center, so they get the new information about their patients, and also, the information about any new visits they need to make the next day.

In the scenario such as this, there’s constantly a need to synchronize the information from a mobile device to a central database.

April 19, 2013