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 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 that needs to be migrated to an older SQL Server version matches that version, the backup can’t be simply restored. 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

How can I take nightly snapshots of SQL server database schema without having to use version control?

A developer aware of the importance of managing database changes knows that database versioning is a must. Having a database versioned in source control enables to recreate the database with the same structure it had at a certain point in the past.

The most common solution for database versioning is using a source control system. But it’s not the only possible solution. Database structure versions can be created even without having a source control system.

April 4, 2013

How to verify SQL database backups

When disaster strikes, the only thing more frustrating than not having an up to date and relevant database backup is having a corrupt backup. When you first create a backup file, it should be good, with ‘should’ being the operative word. Every time the file is copied to another location, there is a risk of file corruption. A foolproof way to ensure that the file is still usable is to restore the backup itself, and run DBCC CHECKDB immediately against the newly restored SQL database.

April 4, 2013

How to create a SQL build script from the source control repository

It’s quite common for developer teams to use database object versioning. The creation scripts for every table, view, stored procedure, and other objects in the database are added to a source control system. That way, everything is versioned and the team is safe.

Applying a specific version of a source control system to a database is not a problem and it can be done by using a source control client.

April 4, 2013

How to restore a SQL Server database backup to an older version of SQL Server

If there was need to restore a SQL Server database backup to an older version of SQL Server there was “greeting” with error messages along the lines of:

Msg 3169, Level 16, State 1, Line 1
Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

or

Msg 3241, Level 16, State 7, Line 1
Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

and the attempted backup restore would fail.

April 4, 2013

How to re-order scripts to avoid dependency-based errors

Having a database scripted to a script folder or to a source control, it enables to use these DDL scripts to recreate it. However, it might not go smoothly due to some missing dependencies.

When creating a database object from scratch, the needed objects are created along the way. The objects can be tied in relationships and constraints to make them dependent on each other.

April 4, 2013

Clean up SQL code from variables you don’t use

Writing and maintaining simple code for stored procedures and functions has never been a problem. However, maintaining complex code, with dozens of parameters and variables, and thousands of rows of SQL statements is. One of the things that the life make easier is to have clean code – without unnecessary and unused parameters and values.

Removing a parameter or variable from code in a production database can cause some problems. They can be easily solved by applying the original object code from a database backup, assuming it can afford the luxury of downtime.

April 4, 2013

How can I make my custom applications self auditing?

SQL developers are increasingly required to include tracking data changes into their custom applications. The information about what change was made – insert, update or delete – who made it, when, and also which computer and application was used, is very useful and can be essential for troubleshooting.

April 4, 2013

Restore only specific data from SQL backups

If there is a need to quickly revert an inadvertent change such as an Update without a Where clause, to recover missing or corrupt data, to move data across development, test, integration and production environments or even just to avoid generating a report using a busy production SQL Server database as a data source, it brings the question if there is a way just to restore specific rows instead of restoring the whole database backup.

April 4, 2013

How to change an object name without breaking your SQL database

Very often, initial database design does not fit the current criteria and requirements anymore. It is therefore necessary to periodically align to what our current needs and standards are. When working with a large database, any idea of changing the name of one or more SQL objects can be a real challenge.

Changing an object name is a simple operation and it could be done easily using the sp_rename system stored procedure or by utilizing the rename and alter … rename statements for indexes and triggers.

April 4, 2013