Visual Studio Database Project Rollback Script

I'm using the Database project in visual studio 2010 to generate a script to deploy my database (and it's changes). This works great.

Is there a way to have Visual Studio database project generate a rollback script as well as the deployment script.

I'm not looking for rolling back the transaction while deploying; but say I deploy it and my stored procedure has an overlooked performance issue that comes up a week later that requires a rollback to the previous version of the database.

Is there a way to generate the rollback script at build/deploy time that will undo whatever changes the deployment script made.

EDIT: If we ignore that I'm using a database project: What is a good way to have an up开发者_StackOverflow社区grade and downgrade path for a database generated?

This generation needs to be part of an automated build process.

To create a rollback script While doing a schema compare using VS2010, It is as simple as swapping the db names specified in the source and target.

This way VS2010 would create a rollback script which would have drop statements against your stored proc.

I've not seen anything like that.

I think you need to reconsider this approach, as you'd still need to fix the stored proc in your database project, otherwise you'd just be re-deploying the "bad" version the next time you deploy. (I'm sure you're already aware of that, but it doesn't help to point out the obvious sometimes!)

If you need to restore an old version of the sproc to the server in the mean time, I would have thought that the easiest thing to do would be to get the previous version from source control and manually deploy that.

You could create a backup of the database before the release and then just restore from the backup if things go wrong. Obviously you'd also loose any data changes (either made as part of the release or subsequently) since the backup was taken.

Another idea I had was to create a snapshot before the release. The operation to create a snapshot is very light weight. I'm not sure you'd want to keep the snapshot for a week, but if the release went wrong then I think it would be quicker to restore from a snapshot than from a full backup. I would be interested to hear any comments people have on this idea.