5

Say I have a hand-written script which performs a bunch of schema changes, eg: (plucked from air):

  • Adds 2 columns to an existing table
  • Creates a new stored procedure
  • Alters an existing stored procedure

And, after running this, we discover that we need to revert back to before this entire script was run.

Is this possible?

In the meantime, data will have been flowing in. We can't throw away the new data (ie: we can't restore a backup of the whole database), but we don't care about any data from the 2 new columns.

I'm looking for a better solution than writing up an explicit "undo" script (with, for example, DROP COLUMN statements and old procedure definitions) every time we make schema changes.

3

There are many good schema comparison tools out there. Redgate Schema Compare and SQL Server Data Tools for Visual Studio are two that come to mind (I'm not affiliated with either).

In a development environment, create two databases which match the the current "live" schema. Deploy your changes to one database only. Use the tool to compare the updated DB to the current DB. Call the generated script "Deploy.sql" or something similar. Now use the tool to compare current to updated -- NB the order has switched. Call the generated script "Rollback.sql".

An important aspect of both roll-forward and roll-back scripts is that they should be idempotent i.e. running the script once or multiple times against the same DB should produce the same output. Generally this requires a lot of "if exists .." statements. The tools can generate these for you.

If space is tight you can use the actual production database as the "current" DB. I prefer not to because of the (small) extra load and the (small) risk of mistakes.

  • I'lll add.. Red Gate compare is transactional too: the ALTERs can be rolled back or committed. That is, the whole script runs or it is all rolled back. SSDT does not do this (as at end of 2016 when I last used it) – gbn Oct 17 '17 at 13:28

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.