Data Compare creating tables?

rossssrossss Posts: 7
Apologies in advance if this is a stupid question. It seems like the sync script in SQL Data Compare is creating tables which I don't want to be sync'd (and not only the dependant tables either), but there are no CREATEs in the script.

I'm trying to bring a copy of a production DB into the development environment for testing, so that I will have up-to-date structures and data. Synchronisation hasn't been good up to now, so I want to start using SQL Compare and SQL Data Compare to keep things up to date going forward, but have no experience with either so far, other than a couple of online tutorials, and my own research into them.

My plan is as follows:
(1) Create a DB as a temporary storage area for just a few dev tables, data, and SPs (mostly all tables/SPs will be ignored)
(2) Use SQL Compare to bring my dev tables and SPs into this temporary DB
(3) Use SQL Data Compare to being the data from the source tables into the temporary DB
(4) Copy the production DB over to the development environment
(5) Use SQL Compare and SQL Data Compare to bring my tables, data, and SPs that are mid-development into this new DB from production
(6) Drop the temporary DB
(7) Detach the old development DB
(8 ) I should now have an up-to-date dev DB, that very closely reflect production, with just a few tables, data, and SPs that are mid-development.

The reason I'm using a temporary DB and not just putting it straight into the destination DB is that I'm not very familiar and want to build up experience as I go, and save time if something goes drastically wrong and I need to recover.

So I've done parts (1) and (2) above, used SQL Compare to bring over the 12 tables and about 40 SPs, it wanted to bring another 4 tables over due to dependencies, so that's fine, we now have 16 tables and ~40 SPs.

I then move onto part (3), load up SQL Data Compare to move the 16 table's data over, but when I sync the data it creates a LOT of other tables that exist in the source DB. These tables were not flagged in SQL Compare to be brought over, and they were not mentioned in the dependencies list in SQL Compare, it's not all of the tables, but still a lot.

I tried letting Data Compare do the sync, and I've also scripted it to SSMS, but in the script there are no CREATE statements, only INSERTs; but it is only when this script executes that the tables are created. This doesn't make much sense to me.

I'm fairly well puzzled as to why this is happening, could anyone offer some insight please?

Comments

  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Thank you for your forum post and sorry that you have encountered a problem.

    SQL Data Compare does not create database objects. So I am puzzled by this statement in your post:
    I then move onto part (3), load up SQL Data Compare to move the 16 table's data over, but when I sync the data it creates a LOT of other tables that exist in the source DB.

    A support call has been created for you, the call reference is F0074536.

    Can you please send an e-mail to support@red-gate.com and include the above call reference in the subject field of the e-mail?

    Please attach to the e-mail, a copy of the deployment script created by SQL Data Compare, screen shot of the SQL Data Compare comparison results and any other information you believe may be useful in resolving the support call.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • That's what puzzled me too, i checked the DB just before running the Data Compare script, and just after, and it does seem to be the script that creates it, but the message pane doesn't match the data pane in SSMS when I script it.

    Thanks for creating the support call, I'm sending an email over now.
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Updating this forum topic in case others users encounter this situation.

    The source database contained some triggers, by enabling these two options in the 'Deployment Behavior' section:
      Disable DML Triggers Disable DDL Triggers

    The rogue tables Rossss mentions in his original post are created due to a number of triggers on database. By disabling the DML and DDL Triggers, the data deployment was able to proceed successfully.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.