Get an alert when a certain record changes in SQL Server

Auditing a database is the first step towards staying updated about database changes. However, if specific, highly sensitive data needs reviewing, an immediate notification of any change is preferable.

Sending email alerts to one or more recipients is one of the most common ways of immediate notification. Sending emails for every change in a database is not advised. It will, in most cases, quickly fill your inbox, and add many unnecessary actions, which can also affect the overall application performance. Therefore, it is important to choose the changes that you want to be alerted about, carefully.

April 4, 2013

Recover SQL data from a dropped table without backups

If you’ve accidentally executed a DROP statement and you have a full database backup, given that no changes occurred after the table was dropped, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the DROP SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data

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

Recover data lost due to a TRUNCATE operation without backups

If you’ve accidentally executed a TRUNCATE statement and you have a full database backup, given that no changes occurred after the table was truncated, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the TRUNCATE SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data.

April 4, 2013

How to secure stored procedures against SQL injection

Although SQL Server’s stored procedures help out with code security by hiding the implementation of the business logic and even protecting against some kinds of SQL injection attacks — primarily those that use an operator such as AND or OR to append commands onto a valid input parameter value, simply wrapping the code into a stored procedure doesn’t mean that applications, database and SQL Server are safe from all types of SQL injection attacks. So, how to make stored procedures bulletproofed against SQL injections?

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 build a SQL database from source control without dependency problems


One of the caveats of having your SQL database under a source control system, is the overhead when the time comes to deploy a new database build. Even if a single copy of the database isn’t shared among the developers, but rather each developer has its own local copy of the database objects’ scripts which are synchronized with source control on a regular basis, building a deployment SQL script may prove to be a rather challenging task.

April 4, 2013

How to migrate a SQL Server database to a newer version of SQL Server

DBAs are well aware that downgrading a SQL Server database cannot be done out of the box. Even when the compatibility level of the database you want to migrate to an older SQL Server version matches that version, you can’t simply restore the backup. What you probably didn’t expect is that upgrading can also be a problem

April 4, 2013

Audit SQL Server database and see who deleted a column value

Every DBA and developer strives to be in control of SQL Servers, databases and data. But, sometimes the situation can get out of control and unusual things start happening.

For example, you have noticed that a value from a specific table column in your database has been disappearing. You’ve checked your code as much as possible and didn’t find anything that deletes the column value, the users say they are not deleting it, you’ve checked the permissions on this table/column and found that no unauthorized deletes were allowed. But something is obviously wrong.

April 4, 2013