Roll Back through Team City

VSinha

1. Added new column with foreign key and checked in
2. TeamCity build was successful – #1.0.102
3. Added test data to new column in Azure database.
4. Dropped the new column and foreign key and checked in.
5. TeamCity build was successful (#1.0.103 ) and column along with data removed from Azure database.
6. Reran the build #1.0.102 with revision number 1450.
7. Build successfully restored the column along with foreign key. However, data in column was not restored.
How to restore the data after doing Roll back while running the previous build in Team City

AlexYates

SQL Compare does not look at the data in your databases, only the schema. If you drop a column the data is gone.

If you would like to be able to restore it you need to make sure you have backed up the data somehow first.

How much data are we talking about?

For small (<1000 rows-ish) lookup/reference/static data, tables where devs manage the data (eg product codes, person type), you can use the static data feature of SQL Source Control to version the data so you can roll it back later but this is not suitable for large tables or transactional data.

If you want to be alerted whenever SQL Compare will drop a table you should be able to set the max warning level to your taste to fail/abort the build. I believe column drops are high level warnings.

VSinha

Thanks for the reply! I am not using SQL Compare. I want to rerun the previous build using Team City. If running the previous build again restores the column with foreign key, then is there any way to restore the data of the previous build of Team City without taking the backup? I don't want to use tools like Data Compare and SQL Compare. I want to roll back using Team City.

AlexYates

Forgive me for misunderstanding.

How have you configured your build? Are you using the Redgate TeamCity plugins to build the scripts maintained by Redgate SQL Source Control/ReadyRoll? If not, what build steps are you using to build/deploy your database?

AlexYates

(Most of the Redgate tools use SQL Compare under the hood.)

VSinha

Yes We use Redgate Team City Plugins hopefully. I wanted to roll back my build through Team City by running the previous successful build in Team City. Dropped Column gets added again but the column data is not sustained. So I was curious to know if we have any way to sustain the data as well while doing the Roll back by running the previous build.

AlexYates

In this case I refer you to my original reply I'm afraid. The TeamCity plugins uses SQL Compare under the hood.

If you deleted the data, SQL Compare cannot magically get it back. You will need to put in place some other process to backup the data somehow and restore it.