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