10

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 upgrade and downgrade path for a database generated?
This generation needs to be part of an automated build process.

QuinnG
  • 6,346
  • 2
  • 39
  • 47

3 Answers3

6

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.

Steve Czetty
  • 6,147
  • 9
  • 39
  • 48
Ankur Mongia
  • 61
  • 1
  • 2
  • 2
    I haven't looked into this much, so it might be obvious: Can this be done in an automated process? – QuinnG Jun 17 '11 at 17:38
0

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.

Mike Goatly
  • 7,380
  • 2
  • 32
  • 33
  • The stored procedure was just an example. It could need to be rolled back for any reason. In some cases a 'hot-fix' can be deployed, others may not work so well for that. (I'm not a DB guy, so my examples are lacking in complexity) – QuinnG Apr 04 '11 at 16:15
  • Ok, that's fine, but I'm not sure that a roll-back script would always work, or be easy to generate without some manual intervention. For example, on previous projects we've needed to migrate data to a new database structure in the pre-deployment stage. This allows us to not lose data when the new database structure is deployed. In order to roll-back these sorts of changes, you would have to manually craft a script to reverse the migration. – Mike Goatly Apr 05 '11 at 06:57
  • 1
    However, if you really do want to go ahead with this sort of thing, you might be able to use the schema comparison tool to generate a script of differences in the *reverse* direction before you deploy. (i.e. from the project to the database). You could then use that as a base-line script if you needed to reverse any of the changes. – Mike Goatly Apr 05 '11 at 07:03
-1

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.

Tom Hunter
  • 5,714
  • 10
  • 51
  • 76