We are using TFS and SQL Server 2005 (looking to upgrade to SQL Server 2012 if that makes a difference). We store our database schema in a Visual Studio Database project (VS 2010).
When code is released to live we currently use the Visual Studio Database Project to build a script for all our schema changes. The problem we have been getting is having to alter or add to that script to add/fix data for the deployment. For example if we add a new non-nullable column to an existing table we need to populate that column with data during the insert. Other times we may want to create new records in transactional tables (e.g. assign specific users to a new security access).
Do Visual Studio Database Projects have a way to store these scripts that only need to be run once and somehow include them in the build? Does it know which scripts need to be run (for example if we are inserting default data we don’t want to do that again a second time)?
OR
Is there a better way to manage these scripts?
2
You could consider a branching strategy in TFS. Create a branch from the main, adding your one off’s into that which you can deploy. Then create a new branch from the main again, deleting or flagging the previous branch. Your new branch from main will not have the once off’s from the previous branch.
Read up on Branch and Merge strategies.