Database Continuous Integration – DeployTest

Part 1 of 4 – Overview

For the next few blog posts I’m going to walk through having our new product OneScript creating a SQL release script automatically after a Subversion commit and then have a process that will automatically do the following:

1. Restore a resent backup of our production database to our Deployment Test SQL Server instance.
2. Apply the release script that OneScript just generated to the database.
3. Determine if applying the script had any errors.
4. Send an email to the developers with “Success” or “Failed” in the subject line. Have the process log as the body of the email and have the results of applying the SQL release script as an attachment.

This will tell the developers almost immediately if the SQL they just checked in causes an error in the release script.

You can download all of the completed code from here.

Most of this is done with PowerShell. If you don’t know PowerShell I highly encourage you to learn it. It is easy to learn and the next version of Windows will not have the command anymore - only PowerShell. I recommend the book below.

Learn Windows PowerShell in a Month of Lunches