VS1010 Database Projects and reference data scripts

Question

Just wondering the best way to handle the following....

I want to have a VS2010 database project to keep the schema of my database in the dev, integration test and production environments in sync.

As part of the test and production environments I have a lot of reference data that needs to be loaded into the database.

For dev and test I can just recreate the database and use Post Deployment scripts to load the data. However, I cant really do this for the production environment as obviously it will have live data on it.

So what is the best solution to do this? I dont think I can use Post Deployment scripts to load the datbase, because in the case of an insert statement I would need to wrap each one inside an IF NOT EXISTS... clause and there are 1000's of rows.

Maybe its best to use the VS2010 + MSBuild tools to keep the schema up to date and then have a seperate solution for managing the data?

Or is there a solution to this that uses purely the tools in VS2010 + MSBuild?

Why are you limiting yourself to "purely the tools in VS2010 + MSBuild" only? — A-K
– A-K, Commented Dec 19, 2011 at 22:38

Oleg Dok · Accepted Answer · 2011-12-19 13:58:32Z

2

The best solution for live production enviroment - not to use automatic updates at all!

Use very well tested hand made update scripts in touch with your backend and frontend applications update

And there is always a good idea to have a fresh backup

answered Dec 19, 2011 at 13:58

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A-K Over a year ago

+1 - you want it to work well, do it yourself. Do not use automatic updates.

Remotec Over a year ago

I think this is the way forward. The current VS2010 tools don't appear to be flexible enough to handle static/reference data.

David Atkinson · Accepted Answer · 2011-12-19 16:03:03Z

0

How about truncating and rebuilding the reference data table each time? If there are constraints you can remove them and add them back at the end of the post-deployment script. Would that work for you?

Or is there a reason why you can't remove production reference data?

answered Dec 19, 2011 at 16:03

David Atkinson

5,9382 gold badges30 silver badges37 bronze badges

Comments

joerage · Accepted Answer · 2011-12-19 17:15:42Z

0

For reference data you can have a script that handles an insert, update or delete depending if the data is already in the table or not.

Check out this link for more details (this also includes a generator to help you generate your scripts).

answered Dec 19, 2011 at 17:15

joerage

4,9334 gold badges41 silver badges48 bronze badges

Comments

gregn · Accepted Answer · 2012-08-06 11:00:05Z

0

Use a populated database to generate merge statements that can be applied in Post-Deployment. It might be a good idea to take out the DELETE clause though.

answered Aug 6, 2012 at 11:00

gregn

1,3601 gold badge14 silver badges26 bronze badges

Collectives™ on Stack Overflow

VS1010 Database Projects and reference data scripts

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related