I have been seeking to get my databases under version control. Does anybody have advice or suggested articles to obtain me began?
I'll always wish to have a minimum of some data inside (as alumb mentions: user types and managers). And I'll most likely frequently desire a large assortment of produced test data for performance measurement.
Martin Fowler authored my personal favorite article about them, http://martinfowler.com/articles/evodb.html. I not put schema dumps within version control as alumb yet others suggest because I would like a good way to upgrade my production database.
For any web application where I'll possess a single production database instance, I personally use two techniques:
Database Upgrade Scripts
A sequence database upgrade scripts which contain the DDL essential to move the schema from version N to N+1. (These use your version control system.) A version_history table, something similar to
create table VersionHistory (
Version int primary key,
UpgradeStart datetime not null,
will get a brand new entry each time upgrading script runs which corresponds towards the latest version.
This guarantees that it's not hard to see what version from the database schema is available which database upgrade scripts are run only one time. Again, they are not database dumps. Rather, each script signifies the changes essential to move in one version to another. They are the script the application of for your production database to "upgrade" it.
Developer Sandbox Synchronization
- A script to backup, sanitize, and shrink a production database. Run this after each upgrade towards the production DB.
- A script to revive (and tweak, if required) the backup on the developer's workstation. Each developer runs this script after each upgrade towards the production DB.
A caveat: My automated tests run against a schema-correct but empty database, which means this advice won't perfectly meet your requirements.
This series from K. Scott Allen may also provide you with ideas:
This is among the "hard problems" surrounding development. So far as I understand you will find no perfect solutions.
Should you only have to keep database structure and never the information you are able to export the database as SQL queries. (in Enterprise Manager: Right click database -> Generate SQL script. I suggest setting the "create one file per object" around the options tab) After that you can commit these text files to svn and take advantage of svn's diff and logging functions.
I've this tied plus a Batch script that can take a few parameters and creates the database. I additionally added some additional queries that enter default data like user types and also the admin user. (If you would like more information about this, publish something and that i can place the script somewhere accessible)
If you want to keep all the data too, I suggest keeping a support from the database and taking advantage of Redgate (http://world wide web.red-colored-gate.com/) items to complete the evaluations. They do not come cheap, but they're worth it.