What are the challenges you face when working across database platforms? Take the survey
Options

Roll Back through Team City

VSinhaVSinha Posts: 10 New member
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

Comments

  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • Options
    VSinhaVSinha Posts: 10 New member
    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.
  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    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?
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    (Most of the Redgate tools use SQL Compare under the hood.)
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • Options
    VSinhaVSinha Posts: 10 New member
    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.
  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
Sign In or Register to comment.