SQL Log problems when Primary Key is not the Clustered Index

Ian McKinleyIan McKinley Posts: 3
edited October 6, 2005 12:21PM in SQL Log Rescue
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
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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?
    · Share on Twitter
  • Ian McKinleyIan McKinley Posts: 3
    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.
    · Share on Twitter
This discussion has been closed.