A couple of weeks ago I said I would release some example source code for managing database change and getting schemas into source control painlessly. Attached is the full source to the tool used in this approach. It's by no means production quality, I literally knocked this up in a couple of hours this evening, but it works and should provide a basis for improvement.
The tool allows you to follow the approach discussed in my earlier post and have your scripts applied to your database as needed. The tool will also generate database creation and update scripts instead of applying them to your database directly - which is handy for packaging releases.
You'll find 3 projects in the solution - an example database project to run the tools against, a command line implementation of the schema tool and the schema engine itself.
To use the command line tool, use the following arguments;
Deepcode.SchemaTool.Cmd.exe - followed by; (items in red are mandatory);
| -h | Display usage information |
| -l [location] | The location of your database project |
| -m [dbscript] | Specify db to run in database update mode - scripts will be applied to the database specified. Specify script to generate scripts to create the entire database and to patch it. |
When running in database update mode (-m db), use these additional arguments;
| -s [server] | The SQL Server to target (default to (local)) |
| -d [database] | The database to target |
| -id [databaseid] | The id of the database in the DatabaseVersions tracking table. |
| -c | If present, instructs the engine to create the database if it doesn't exist. |
| -cd | If present, instructs the engine to create the database. If it exists already, it will first be dropped. |
| -u [username] | The SQL username to login to the server as - if not present the system will use windows authentication as the current windows identity. |
| -p [password] | The password to connect to SQL server, when -u is being specified |
When running in script generation mode (-m script), use these arguments;
| -o [location] | The location to store the generated SQL scripts. |
To use this as part of your development process, place a copy of the exe into your trunk/library directory (or whatever strategy you use for SCC of dependencies), and create two batch files - one to update the database and one to generate the scripts. Then, whenever you make a change, just run the update script. When you release a version, run the generation script.
I didn't have time to do it tonight, but it should be trivial to wrap the same functionality as the command line host into a custom MsBuild task and automate everything. If anyone does this, please post a link to your wrapper source.
And finally, again, remember this isn't production quality, I offer no warranty etc...use at your own risk.
