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. When I run the script from Data Compare in SSMS, the messages pane is full of create statements and no inserts, but the actual code pane is only inserts and no creates, they don't match! Even though the inserts are inserting data, the extra tables are also created.
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?