Best way to create a SQL Server rollback script?

Question

I am working on some schema changes to an existing database.

I backed up the database to get a dev copy, and have made my changes. I will be creating a single roll script to migrate the changes on the production machine in a single transaction.

Is there a best practice for creating a rollback script encase a deployment issue arises? Before I've written then by hand using the following pattern:

• Drop new constraints and indexes
• Alter tables to remove new columns
• Drop added tables
• Commit transaction

Is there a better approach?

Answer 1

You are missing the fifth step

• Drop new constraints and indexes
• Alter tables to remove new columns
• Drop added tables
• Commit transaction
• Test the hell out of the script before running it in production

A more efficient approach is to register the changes as they happen like RoR migrations do. For each DB change you create a script that will both apply the change and roll it back (at your choice, of course). You can then have those scripts under version control just like your code.

Additionally, if you keep a version number in the database you can automatize it a bit more, by identifying each script with a version number and having it increase or decrease the version number in the database according to the performed action.

Answer 2

That's basically it, I don't think there's much to add, aside from what your approach. This is how we do it in our company, we developers are responsible for creating the script and the rollback script, and we are responsible for leaving the DB in the same state it was before the initial changes are applied. Then the DBAs run it in production, if there's ever a problem they'll run the rollback script and everything is back to normal. Just keep in mind the dependencies and the order in which you alter your objects and then create the rollback script backwards.