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?
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