I am in the process of designing a daily build routine for my project. We are using Flex 3 and SQL Server 2005. I am unsure to what degree I should be rebuilding the database on a daily build. Should I be dropping all tables and stored procedures and adding them back in? Then I would have to have a script that put all the default data back into the tables.
Or should I have the DBA write Alter scripts that only run when changes are made and concentrate on building the code on a daily basis?
This really depends on your current stage of project development.
While in active development without any production deployments the simple thing to do is just drop and re-create your database every time. This way your code and database should be in sync, especially if you are doing unit testing. This is basically doing a trunk build i.e. latest and greatest. It is much simpler to maintain. Scripting your default data is easy and a great way to have a clean setup when you need one. Check out the sp_ generate_inserts stored procedure for a nice utility to script the data from an existing database.
Once you have production databases and will be altering you database with code releases, it's important to test change scripts also. Change scripts tend to be a bit tougher to write due to dependencies and data manipulation considerations.