Let us say you will find there's continuous integration server. After I sign in, the publish-hook pulls the most recent code, runs the tests, packages everything. What's the easiest method to also automate the database changes?
Ideally, I'd build an installer that may either develop a database on your own or update a current one with a couple automated syncing method.
If you possess the chance to define and control the entire database management and db creation process, have serious consideration at DB Ghost - it's not only something - it is a process.
If you want it and may implement it, you will get great returns onto it - but it is a little of the "all-or-nothing" type of approach. Suggested.
I'd caution against utilizing a db backup like a development artifact, most CI guidelines claim that you manage the schema, methods, triggers, and sights as top class development items. The unwanted effects is you can take that one step further and employ these to develop a new database without notice, ideally you might also need some data that may be pressed in to the database.
This is a high cliff notes version to obtain your ft wet, but there's lots available within this space: http://www.infoq.com/news/2008/02/versioning%5Fdatabases%5Fseries
I love a few of the ideas that Scott Ambler has because well, the website is nice however the book is remarkably deep for this type of difficult group of problems. http://www.agiledata.org/ http://www.amazon . com.com/professional/obidos/ASIN/0321293533/ambysoftinc
The Red-colored Gate approach using SQL Source Control and also the SQL Compare Professional command lines are detailed with code samples here: http://downloads.red-colored-gate.com/HelpPDF/ContinuousIntegrationForDatabasesUsingRedGateSQLTools.pdf
Troy Search authored articles on Simple Talk titled "Continuous Integration for SQL Server Databases": http://www.simple-talk.com/content/article.aspx?article=1247
The most recent version (5.) of DB Ghost does not are afflicted by the "non ASCII character" problem (it simply implies that the file is UTF8 encoded) also it should have the ability to just do the thing you need.
Also, the various tools can really be utilized stand alone to do the different functions (scripting, building, evaluating, improving and packaging) if you would like, it is simply that with them altogether supplies a full finish-to-finish process thus making the general value more than the sum of the it's parts.
Essentially, to create changes towards the schema you update individual object creation scripts and per-table place scripts (for reference data) which are held under source control exactly like you were creating a “day one” greenfield database. The DB Ghost tools are utilized to let the whole factor because they build these scripts right into a completely new database (using continuous integration if needed) after which evaluating and improving a target database, which may be a duplicate from the production database. This method creates a delta script which may be utilized on the actual production database throughout go-live.
You may also create a Visual Studio database project and add it into any solutions you presently have.
Red-colored Gate is really a quite robust solution also it calculates from the box. However the best factor is you can integrate it together with your continuous integration process. I personally use it with Msbuild and Hudson. rapidly explaining how it operates: http://blog.vincentbrouillet.com/publish/2011/02/10/Database-schema-synchronisation-with-RedGate
if you want to learn more relating to this, you can request
Perhaps you have checked out FluentMigrator? The default download includes Nant scripts that might be simple to add in a CI. Free, free and simple to use. Works best for a multitude of databases.