SSDT creating a rollback deployment script?

Question

we can create a SQLServer deployment script with TFS&SSDT, but is there a way to create a rollback script, so that we can roll back the deployment? Thank's

Answer 1

As SSDT (and similar products) all work by comparing the schema in the project against a live database to bring the database in sync with the model, there's not a direct way to create a rollback script. There are also considerations regarding data changed/added/removed through pre or post-deploy scripts.

That being said, there are a handful of options.

1. Take a snapshot each time you do a release. You can use that snapshot from the prior release to do another compare for rollback purposes.
2. Maintain a prior version elsewhere - perhaps do a schema compare from your production system against your local machine. You can use that to compare against production and do a rollback.
3. Generate a dacpac of the existing system prior to release (use SQLPackage or SSDT to do that). You can use that to deploy that version of the schema back to the database if something goes wrong.
4. Take a database snapshot prior to release. Best case scenario, you don't need it and can drop the snapshot. Worst case, you could use that to rollback. Of course, you need to watch out for space and IO as you'll be maintaining that original state elsewhere.
5. Run your changes through several environments to minimize the need for a rollback. Ideally if you've run this through Development, QA, and Staging/User Acceptance environments, your code and releases should be solid enough to be able to release without any issues.

You'll need to code accordingly for rolling back data changes. That could be a bit trickier as each scenario is different. You'll need to make sure that you write a script that can undo whatever changes were part of your release. If you inserted a row, you'll need a rollback script to delete it. If you updated a bunch of data, you'll either need a backup of that data or some other way to get it back.