Base Database implementation

I am currently looking to buy or build software that enables my firm to:

Run a set of scripts to create a base database - empty database with minimum amounts of data (i.e. lookup data and seed data). So that when a new development project begins all that is needed to build the database is to run these scripts.

The challenges we face is managing these scripts. It would be easier from a manual perspective to only have 5-6 scripts, so that we do not need to manually open and execute each script (representing an object). The problem with this is when you modify a stored procedure, you modify it in your version control platform (which is single object based) and then you need to find and replace the object in your script.

What we are looking to do is buy or build an application to manage these files for us. I was wondering if anyone has purchased a solution to accomodate this?

We currently have ApexSQL Diff - which works well to compare contents of databases (structurally it does well, seems to have a problem comparing data but that is another discussion). What it does not do well is script out an entire database, and if it were to do this, it would put it all in one file.The aPress book Real-World SQL-DMO for SQL-Server, by Mitchell & Allison, describes (and includes source on CD) the VB code to perform this very task. You would create your "empty" database once. Then, run the tool against the database to script, not only the database structure, but the data as well.

Then, just run the script on a new server to create your database.|||Thanks I just purchased that book based on your advice, we had a meeting to further discuss, this is what we came up with:

I. Structural
We agreed using a tool like Erwin is the best approach, have 1 point person (with a backup in place) that would own the database. When development teams are ready to merge up they would tell the point person which DB their project uses. The point person would then use Erwin to merge their structural changes into the master data model. Once that is complete, scripts can easily be generated to serve as master install scripts for new projects into empty DBs.

II. Functional
Currently we use subversion to store our views, UDFs and stored procedures. Regarding this piece we discussed creating a simple program to manage the creation of these scripts using the individual Subversion files as the repository. I purchased a book that comes with a CD that has VB code that utilizes the SQL DMO in order to create the necessary scripts. Ideally we would want an application that takes an INI type file with a list of objects and generates the necessary files to create those objects. This could be used in two situations the first would serve as a solution to the base DB install for UDFs, Views and Stored Procs. The second situation would be to aid in managing changes in the Development environment when we need to push changes to QA in the form of install scripts. Currently we have a very tedious process of generating the necessary SQL per object and having to find the object in our original install script, delete what is in the script and paste our new code into the script. By having an application in place to automate this process would be a great aid in generating the Base DB scripts and even more so with regards to ongoing developing.

III. Data
Depending on how the Functional phase described above goes (the aforementioned book also has code to perform similar actions on data) we would look to apply similar functionality to seed the Data. As an alternative we could look at automation of BCP file creation. There are two types of data mandatory lookup data that would also be used for QA / Production releases, and non-mandatory data, i.e. seed data, that would be used to improve basic functionality of the 3 sites.

IV. Test Scripts
This portion would need to be revisited when the time is appropriate I would like to look at solutions that would help in automating test scripts. Ideally the purpose of this phase would be to ensure all three sites are working post-merge. The easiest way to ensure proper functionality is to run automated test scripts that can return a PASS or FAIL result, rather then for each project having to manually follow a test plan.