SQL Server rollback schema update

Question

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.

Answer 1

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.