Jim Aho February 2016

How to modify actual data once only from a SQL Server Database Project?

I have a SQL Server Database Project in Visual Studio 2013 with a couple of;

  • Tables
  • Stored procedures
  • Some PostDeployment scripts

I will leave the tables and stored procedures now, and focus on the PostDeployment scripts, as I feel those are the ones most interesting for my question.

Say for example I want to remove some data that was previously caused by a bug. Now the bug has been fixed but i'd like to set the database into a better state by removing the "wrong" information. What I need is script that is executed once-only ever. With a change-script approach this would be trivial, but I cannot find a solution for this type of database project.

PostDeployment scripts are executed after each deploy, meaning I cannot really do any meaningful once-only change to the actual data here.

I could include an IF statement in the scipt to check the data before making the change, preventing it from being executed multiple times, but I'd prefer to rely on the tools rather than a user-written IF statement.

I feel my use case is rather simple, yet I cannot find a solution for it. How shall I approach this? Thanks for all tips!

Answers


Jim Aho February 2016

Credit to Pieter Geerkens:

Write an idempotent script - developers do this on a regular basis to manage exactly the problem you describe.


This seem to be the way to. The reason why I didn't want to take this approach at first is because it will execute the script during each publish. In some future if the data again satisfies the conditions data might again be updated / deleted / removed, and I'd prefer this not to happen.

For reference: Idempotent

Post Status

Asked in February 2016
Viewed 2,183 times
Voted 5
Answered 1 times

Search




Leave an answer