Red Gate forums :: View topic - SQL Log problems when Primary Key is not the Clustered Index
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Log Rescue
SQL Log Rescue forum

SQL Log problems when Primary Key is not the Clustered Index

Search in SQL Log Rescue forum
Post new topic   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
Ian McKinley



Joined: 06 Oct 2005
Posts: 2

PostPosted: Thu Oct 06, 2005 4:52 pm    Post subject: SQL Log problems when Primary Key is not the Clustered Index Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Thu Oct 06, 2005 5:06 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Ian McKinley



Joined: 06 Oct 2005
Posts: 2

PostPosted: Thu Oct 06, 2005 5:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group