It always annoyed me that there is no way to output schema to a file from SQL 2005 Management Studio. It has the the basic functionality to script out simple objects like table, or database (just the creation of the database itself, nothing in it), but you have to do one object at a time. On top of that, there is no way to output the data into a script either. Sure, you can do a backup and restore your database, but this solution is just silly if you have to do it a few times each day. I just want something that can output DDL and DML to a file for all the objects in the database.
In the past, I wrote a CodeSmith template to do this. It took me a few days of messing around to get everything generated correctly. Basically it allows me to point at an existing database to script the schema and data into a file. The generated script goes into our MSI every build, so the client can create the complete schema during the installation. The template did the job, but it did not do it the most efficient way, so it was a little bit slow when there was a lot of data. It can take three to eight minutes on a large database. The output script was not in the most robust form because it assumes that you have a blank database (does not check if object already exists). I am sure if I had more free time, I could have done it better. (I enjoy my free time at home doing other stuff :-)
Database Publishing Wizard does exactly what I wanted and more. I wish this tool would have came sooner. I tested it out on one of our databases and it ran much faster then my CodeSmith template, and the output script is much more robust. It took just a few seconds to generate the whole database schema with a good amount of data. It also supports command line interface, so it is perfect for the build server. I think we can actually speed up our build by using this tool instead. Currently the tool supports all unencrypted objects in the database.
Schema generation is what I am mostly interested in, but it does more than that. You can actually output the schema to a remote database via web service. If you are interested, the full documentation can be found here.