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.

  • Which RDBMS are you using? – Philᵀᴹ Oct 12 '12 at 9:46
  • Sorry, MS SQL 2008 – plurby Oct 12 '12 at 9:57

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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