asked
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.
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.
+1 - I couldn't write that much before the coffee starts to kick in...
[InternalsViewer][1] might be of help [1]:
http://internalsviewer.codeplex.com/wikipage?title=Transaction%20Log%20Viewer&referringTitle=User%20Guide
Thanks. I am sorry to say that i can't use it with SQL Server 2008 R2.
9 People are following this question.
