Compare live vs backup does not generate similar scripts

nate.johnson

Hello,

I am trying to automate our database upgrade process by shipping a database backup and comparing with the customer database.

I have a working utility but the problem I am running into is that when I do a data comparison of a certain table using a FK as the comparison key, I get different results depending on whether I am comparing with a live database or a backup of the same database.

It seems that the FK is not being honored and SQL Data Compare is attempting to use the PK on the table.

Have you seen this? If not I can try to provide an example.

Matthew Flatt

Hi Nate,

Thank you for your post into the forum.

Are you running your comparison/synch from the command line or SDK?

When comparing to a backup file using SQL Data Compare, custom comparison keys can not be specified and therefore this is why your PK is being used rather than the FK you specified.

This should account for the differences in the script produced and I presume you are not aware of this because the GUI is not being used?

I hope this helps to clear things up.

Thanks,

nate.johnson

Actually, this doesn't clear things up. I was using the SDK when I discovered the problem so I tried it in the GUI. Now that I look it over again, I see I am trying to use an index as the comparison key. The GUI allows you to specify either the PK or any of the indexes listed but has no option for a custom key in the dropdown when using a backup.

When I select an index as the comparison key, it falls back to the PK.

Sorry for the confusion, but the GUI should not allow selection of an index as comparison key if it is going to use the PK anyway.

Matthew Flatt

Hi Nate,

When you select the alternative index in the GUI and perform a compare does it revert back to the PK or is this just the behaviour in your SDK project?

Thanks,

nate.johnson

This is happening in the GUI as well.

Matthew Flatt

Hi Nate,

Is the Index you are selecting Unique or Non Unique?

When you select the Index and run a comparison in the GUI, what value is on the left of the Row Differences pane?

The column of the Index or your PK?

Thanks,

nate.johnson

The left column shows the first portion of the index. The index is unique.

It looks like the PK isn't being used for comparison either. I checked the rows that are showing as identical and the PKs are different.

Matthew Flatt

Nate,

Would it be possible for you to send a SQL Compare snapshot of your database schema and the backup you are comparing against (If it isn't too big) to support@red-gate.com quoting reference F0015522 ?

Thanks,