x

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.

Regards.
more ▼

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

yaqubonnet gravatar image

yaqubonnet
247 16 17 20

(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

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

+1 - 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

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1840
x67

asked: Sep 07, 2012 at 06:40 AM

Seen: 1119 times

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