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

Before making any changes to a database project I take a snapshot (a dacpac) which I can compare the modified database project against to generate a release script. Although it's easy enough to swap the source and target to do a reverse schema compare I've discovered it won't let me generate an update script (which would be the rollback script) from the reverse comparison, presumably because the target is a database project.

To get around that problem and generate a rollback script I do the following:

1. Deploy the modified database project to my (localdb) development database;

2. Check out a previous version of the database project from source control, from before the changes were made;

3. Run a schema compare from the previous version of the database project to the (localdb) development database;

4. Use the schema compare to generate an update script. This update script will be a rollback script.

Although it would be nice to be able to generate a rollback script more directly the four step process above takes less than five minutes.

Answer 2

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.