Someone asked me recently if I stored backups of my database in version control. This person wanted to recover from a bad deployment and use a backup to do so. They felt that keeping a copy of the backup in a VCS, alongside the code being deployed, would be important. It might be, but I said that recovering from a bad deployment isn't something I want to do with a restore if I can avoid it. Then I was asked how to recover from a bad deployment if you have a busy, 24x7 environment.

I, of course, answered, "it depends."

It does depend on the deployment, but it also depends on your preparation. There are ways in which you can work to minimize the problems that might occur during a deployment. Obviously testing your scripts and deployment process is important, but it's also good for you to understand how your scripts work and what techniques you can use to rollback problematic deployments.

There's a switch in SQL Compare that lets you build a deployment script, and then immediately generate a rollback script. It's handy, but it's also not going to always work. If you've added a column during deployment, you might not want to just remove it on rollback.

However you can prepare for issues, like having a script that might save data in the new column before you remove it. You might choose to copy the table as part of a pre-deployment process (or during deployment), having this copy of the table used in a rollback scenario. You might even bring up a warm copy of your database and prepare to swap entire databases if problems arise. This would allow you time to save and move data that was changed after your deployment, but before your rollback.

There are lots of possibilities in how you might recover from a failed deployment, but as with many of the solutions that we build in technology, a well thought out plan makes everything run smoother.