don't delete items in destination db not in source

khaihoang · January 6, 2010 11:56PM

I am using SQL Data Compare 7, how can I tell the SQL Compare not to delete items in the object sync script, when the object is in the destination db but not in the source db ?

Thank you.
Khai

Eddie D · January 8, 2010 5:35AM

Thank you for your post into the forum, however I littele confused as you inform that you are using SQL Data Compare and ask how to remove objects from SQL Compare. Although the products are similar in the way you select the data sources for comparison they are in fact two different products.

In SQL Compare, to remove objects from the synchronization that exist only in the database to be synchronized. Perform the comparison and wait for the results to be displayed.

The results will be spilt into 4 sections:
Objects that exist in both data sources but are different.
Objects that exist only in data source one or left hand data source.
Objects that exsist only in data source two or right hand data source.
Objects that are identical.

So if you are synchronizing in the direction of left hand data source to the right data source. Expand the section for Objects that exsist only in data source two or right hand data source and uncheck the check boxes so that the object will not be included.

Also when you run the Synchronization Wizard, look to see if the objects that you do not wish to be removed, are not included in the dependancy list as the object will be include. Uncheck the 'Include Dependencies' option.

For SQL Data Compare, the results returned are similar to SQL Compare, in that you will for a table or view,you will have:

Rows of data that exist in both data sources but are different.
Rows of data that exist only in data source one or left hand data source.
Rows of data that exsist only in data source two or right hand data source.
Rows of data that are identical.

So if you are synchronizing in the direction of left hand data source to the right data source. foe the table or view, look at the centre 3 columns:

Rows of data that exist in both data sources but are different, the results are displayed in the middle column.
Rows of data that exist only in data source one or left hand data source, the results are displayed in the column immediately to the left of the centre column.
Rows of data that exsist only in data source two or right hand data source, the results are dispalyed in the column immediately to the right of the centre column. So if you wish these rows of data to remain, uncheck the check for the table or view.

I hope the above answers your question. further advice can be found in the SQL Compare and SQL Data Compare help files.

Many thanks
Eddie

khaihoang · January 8, 2010 6:36AM

Thank you a lot for your quick response.

With the way you described, I have to check the objects one by one whether there are rows of data that exist only in data source two or right hand data source, and exclude the changes before synchronizing the databases.

I meant that if there is an option to tell SQL data Compare to compare and sync data one way from left to right. I am having a request to sync data from left to right and disregard the changes on the right hand data source. And I have to sync more than 100 tables in my database.

Thank you.

Eddie D · January 13, 2010 6:52AM

Thank you for your reply.

When using the SQL Data Compare GUI for the first time, all of the possible results are selected for synchronization.

Once you deselect the rows of data for each object, that exist only in the target. When you close the GUI. The selections you previously selected are saved. The next time you open the GUI, the options you previously set will remain, therefore in your example you would only need disregard the data that exists in the target or right hand data source just the once for that project.

Alternatively, if you are licensed for the professional edition of SQL Data Compare, you can make use of the Command Line Line interface and create a script that will compare and synchronize the rows of data that exist only in data source 1 and exist in both but are different. As per this simple example:

sqldatacompare /Database1:WidgetDev /Database2:WidgetLive
     /Synchronize /Include:Missing /Include:Different

Have the script in a batch file and call the batch file when you need it. For more information on the SQL Data Compare Command Line Interface, please take a look in the SQL Data Compare Help File.

Many Thanks
Eddie

khaihoang · January 14, 2010 3:15AM

Thank you very much. Looks like the professional edition of SQL Data Compare can meet my request.

don't delete items in destination db not in source

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk