Automatic Database Script Execution

We are using an automatic database script execution mechanism such as the one recommended by Scott Ambler.  The basic working of it is that every database refactoring is placed in its own script file and that each file has a sequence number.  We also store the sequence number of the last run script in a table called Version along with a column called DatabaseName that we can query to find out which database we are running the script against.

When the automated deployment takes place, our PowerShell script will automatically run all the scripts since the last one that was run against that database and update the version number.

During an automated build, we actually create the entire unit test database from scratch with a few initialization scripts and then apply all the update scripts in sequence to arrive at the latest database schema.

We also have a script that we us when we download a production database to our development machines, to bring that database up to speed with the latest scripts to match our code.

I recently had an issue where I needed to create a stored procedure on the unit test database that I didn't want in production.  This procedure wipes all the data in the database except lookup data.  I needed this when using recorded tests from Selenium, because unlike our other tests, we can't do the entire test in one transaction and I needed to clean up after the test.

In order to achieve this I used some dynamic SQL like so (thanks for the tip Schalk):

if dbo.GetDatabaseName () = 'policy-unittests'
BEGIN

    DECLARE @Sql NVARCHAR(MAX)
    SET @Sql = 
    'CREATE PROCEDURE CleanDatabase
    AS

        -- Lots of delete statements
        DELETE FROM Policy
        DELETE FROM Person'
        
    EXECUTE sp_executesql @Sql

END