How can i get the very first value of a column from transaction log in sql server?

I am using SQL Server 2008. I have a column "tableOrder.quantity". It’s a Non nullable column so it always have value at the time of creation/modification.

Now we require checking "tableOrder.quantity at the time of creation value" for some of the records. We don't have backups!!! So my question is "Can we find that value in transaction log" & HOW?

For example order1.quantity was 10 at the time of creation. But then its value is modified multiple times for example quantity is changed to 100 then to 1000 & so on.

We need to get the very first value which is 10 in this case.


more ▼

asked Sep 07, 2012 at 06:40 AM in Default

avatar image

247 16 17 24

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If the transaction log that covers the period when the row was created is still available then yes as @ThomasRushton says you could use tools to view it, but I'm not sure what you mean by not having backups?

If the database is in SIMPLE recovery mode, then the chances are the transaction log has been flushed as there will have been a checkpoint (whilst I write this at least) - in this case you can't get the original value.

If the database is in FULL recovery mode, and you mean you are taking backups but dont have the transaction log backup from the creation time - then again no, you've no chance.

If the database is in FUll recovery mode, and you are not taking backups at all - you may have a chance, but you are storing up big issues for later (thats what @Thomas means) and you need to address your backup/restore strategy now. Today.

It sounds like what you need to do is a change in the requirements - this functionality needs to be planned and designed into the database.

more ▼

answered Sep 07, 2012 at 08:04 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

  • I couldn't write that much before the coffee starts to kick in...

Sep 07, 2012 at 08:35 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

InternalsViewer might be of help

 <insert standard rant about backups>

more ▼

answered Sep 07, 2012 at 07:38 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

Thanks. I am sorry to say that i can't use it with SQL Server 2008 R2.

Sep 07, 2012 at 09:22 AM yaqubonnet
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 07, 2012 at 06:40 AM

Seen: 1342 times

Last Updated: Sep 07, 2012 at 09:39 AM

Copyright 2018 Redgate Software. Privacy Policy