Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

During the development of applications users often want to add new modules, business rules etc. So I've often used SQL Server 2008 option to create scripts from the development DB to generate new tables that I will then execute on the production server before I publish the new version of the application.

So I'm wondering is there a way to put the whole generate script file in the transaction and rollback changes if there's an error returning everything to the previous state, no new tables added, data inserted etc.

If there is a reason that this isn't a recommended approach please explain.

share|improve this question
Which RDBMS are you using? – Phil Oct 12 '12 at 9:46
Sorry, MS SQL 2008 – plurby Oct 12 '12 at 9:57
up vote 5 down vote accepted

In theory you could do something as like wrap the script execution in a transaction. You can execute the .sql batch files from an application library like dbutilssqlcmd or SMO's ServerConnection.ExecuteNonQuery which handles the GO batch separator or sqlcmd extensions in the script.

However in practice this is nearly impossible to do. Wrapping a script in a transaction may yield unexpected results because the locking changes. Certain DDL statements are not allowed inside a transaction. The script may contain sqlcmd extension directives like :connect which would mess up the entire transaction handling.

Another approach is to use migrations, which are coded in the application and handle both the roll forward and the roll back of the change. But to be effective the steps must be idempotent (yield the same result even if invoked multiple times) and this is very hard to achieve in practice.

The safest and also easiest approach is to rely on a backup taken prior to migration, which would give the ability to rollback to the backup if anything goes wrong during the deployment of V. next. This is very easy to implement and very efficient if database snapshots are used as backups.

Of course if there is a 24/7 availability requirement things change dramatically, but rolling out upgrades with no downtime is a subject much bigger that this post and solutions roll in the $millions, unlikely something you would fancy.

share|improve this answer
Agree on the DDL statements and migrations approach. Great answer, thanks. – plurby Oct 12 '12 at 11:23

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.