SQL Log problems when Primary Key is not the Clustered Index

[Ian McKinley]

Ive noticed that when I try and test SQL Log Rescue (by say updating a database with some incorrect column values) then running Log Rescue to recover - it doesnt work correctly. This appears to be because the table in question has a clustered index which is not the primary key. Log Rescue seems to use the clustered index for uniqueness, which is a bit of an assumption. If I switch the primary key to be the clustered index, everything is fine. I was wondering if anyone else has noticed this.

thanks
Ian McKinley

[Brian Donahue]

Hello Ian,

I don't think this would happen all of the time, and will probably depend on your schema. Can you tell me what kind of updates the UNDO operation is generating (INSERT/UPDATE or combination?) and any warnings that appear in Log Rescue's script?
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Ian McKinley]

its generating updates like the following:

UPDATE [dbo].[NLDeferredNominalTran] SET [AccountDepartment] = 'IAN'
WHERE UniqueReferenceNumber = 1234

The trouble is uniquereferencenumber is not unique (ironic I know) for this table and it is not the primary key (it is however the clustered index).

There are three warnings generated:
Triggers are not disabled
Foreign keys are not disabled
Cascade deletes are not disabled.

But to be honest, I cant see any of those affecting my update.

By the way - ive noticed another issue after I sent the last message - If I rollback a dropped table, it restores the data correctly but non of the non-clustered indexes are re-added.