Using sqlHawk for database versioning

Recommended process for using sqlHawk to apply database versioning.

You can find a windows batch file based example here: https://gist.github.com/3164291

Applying to an existing database

Use the scm output to put your existing procedures, views and functions into source control.

Add the version table to your database using supplied scripts, or manually (not available yet). Set the version to 0.

Build server setup

Set up your builds (using a continuous integration server such as TeamCity or CruiseControl.NET) to do the following nightly:

• back up your live database to file
• drop your test database
• restore your test database from the live backup
• run sqlHawk to apply outstanding changes to your test database (functionality not yet available in sqlHawk)

Set up an on-demand build for deploying to live which will

• back up your live database to file
• run sqlHawk to apply outstanding changes to your live database

Here's some example scripts for building a Sql Server database with SqlHawk: https://gist.github.com/3164291

Using change scripts

See scripted upgrades

• Create a Scripts folder next to the Procedures folder.
• Add a text file to the folder named 1_foo.sql
• Enter the DDL for the change you want made (perhaps using external tools).
• check in your change
• Tell your build server to run your "test" deployment.
• Check version table on test database
• Check your changes have been applied
• Run your live build.

Bonus Points

• Tag your releases in source control
• Use different branches for test and live and have your build server check out the right version for each build
• Add more test environments and branches.
• It's up to you whether you want to stop anyone making changes on live without using the scripts. The above approach will work fine even if people change live so long as nothing conflicts with the change scripts (apart from procs etc which will get ruthlessly rolled back - yay). If there is a change made to live that breaks the scripts you should find out on the next build of test. I'd recommend denying ddl on live if you can.