Problem using PreUpgrade script in TeamCity's Redgate SQL Change Automation Build

ybhole

When I push Preupgrade script to database in GIT using SQL Source Control 7, I see TeamCity's Redgate SQL Change Automation Build runner type which builds database and creates a nuget package fails as in preupgrade script I am inserting some data to the table...... but all tables being dropped before building a database and hence I get exception

The error 'Invalid object name 'dbo.MyTableName'.

So how to write a preupgrade script for an existing tables, as Teamcity will always throw exception on preupgrade script as tables are being dropped first while building a database.

Dan B

Hi ybhole,

Thanks for posting on the Redgate forums.

At the start of the schema validation step all of the user objects are dropped.

To get around this problem you would either need to use some Data Manipulation in the Post-Deployment Script or another method could be to use T-SQL that checks for the existence of the tables before attempting to manipulate data.