I ran across a very handy open-source tool today that I thought I'd share... but first some background...

Our current project requires that we support 4 simultaneous run-time configurations - Development, Testing, Training and Production. The application has the capability to switch between these on the fly, via  a convenient pull-down menu. 

Changing the configuration is actually somewhat simple - we just close the current map document, set a new "repository connection" and refresh the UI. From there, the application will start using the configuration in the active repository. Great.

Well, today I needed to actually create these 4 repositories. More specifically I needed to create 4 databases with the same schema AND the same data (as a starting point anyhow). In the past I had used a CodeSmith template that would write out both the schema and all the insert statements to re-populate it, but that was a pain to use. I did a little searching and found the Database Publishing Wizard which is a part of the SQL Server Hosting Toolkit on CodePlex (you can get the source!).

From the site...

The tool supports two modes of deployment:

1. It generates a single SQL script file which can be used to recreate a database when the only connectivity to a server is through a web-based control panel with a script execution window.
2. It connects to a web service provided by your hoster and directly creates objects on a specified hosted database

For me, I just needed the SQL script that would re-create the entire database with data. What's nice (besides being free) is that this integrates into Visual Studio's Server Explorer...

The the output is a text file with all the SQL to re-create the tables, views, stored procedures etc. and their contents. I was able to just drop this into SQL Management Console, and run it against the other 3 databases. Since it's just T-SQL, I can also upload it to our client's site, and run it there to populate their databases as well.

Command Line

A final gem in this tool is that it can be run from the command line - thus allowing you to effectively source control a database schema and data. While you would not want to do this for spatial data, it would work very well for configuration data. You could write a simple MSBuild script that would check out the copy from source control, use this tool to dump the database to that file, and then check it back into source control. Then you could execute this script anytime you want to update the copy in source control. Anyhow - the command line give you lots of options for integrating into your development process.