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?

Answer 1

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.

Answer 2

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).

Answer 3

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?

Answer 4

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