SQL Log problems when Primary Key is not the Clustered Index

Undo for SQL Server.

Moderator: eddie davis

SQL Log problems when Primary Key is not the Clustered Index

Postby Ian McKinley » Thu Oct 06, 2005 4:52 pm

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
Ian McKinley
 
Posts: 2
Joined: Thu Oct 06, 2005 4:47 pm

Postby Brian Donahue » Thu Oct 06, 2005 5:06 pm

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
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Ian McKinley » Thu Oct 06, 2005 5:21 pm

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.
Ian McKinley
 
Posts: 2
Joined: Thu Oct 06, 2005 4:47 pm


Return to SQL Log Rescue

Who is online

Users browsing this forum: No registered users and 0 guests