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

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.

Answer 2

If it's a relatively small database just make a backup before you apply the upgrade. If it all goes haywire you do a restore.

Once it goes live and new data is entered you can't really roll it back anyways. You just have to fix the problems.

Answer 3

You are missing the sixth 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

An option to do all that less manually 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 4

If you use a tool like Redgate's SQL Compare, you can run the compare between the two databases both ways and use both to create the scripts you need. the dev to prod script would have the changes you are making and the prd to dev script would return to the orginal production database state. This ensures you don't forget anything in either script as well.

Answer 5

With SQL Server 2005 or newer you can also create a database snapshot, that allows you to go back to that state at any time. See this page for more information:

http://msdn.microsoft.com/en-us/library/ms175158.aspx