|
I'm pretty sure the answer is "no", but a client was asking, and now I'm curious. |
|
The longer answer is also no. Here's why: Lets say you start with a empty datbase D0. Then you add a number of transactions T1, T2, T3, ..., Tn. The state of the database after the nth transaction is Dn. The database file Dn only contains the final state of the data after Tn (and all prior Ti for i <= n) has been committed. There is no information within the database file that would indicate what the previous states D1, D2, .... Dn-1 were. Therefore there is no method of determining what the transactions were to get to Dn. Putting the above into a concrete example, lets say you insert a row into a table T ( c1 int ) with c1 = 1 and then commit the transaction. The log file would indicate that you had inserted the row but the database just says that there is a row with c1 = 1 and has no indication of how the row got there. Now lets say that we update this row and set c1 = 2. The transaction log now has two transactions 1: insert into T( c1 ) values ( 1 ), and 2: update T set c1 = 2 where c1 = 1, but the database only holds the final state showing that there is a row with c1 = 2. All prior information about a row with c1 = 1 is gone. I.e. if we were to look at the final state of the database we would not know that there was ever a row with c1 = 1. I should stop here but I'll mention that the database may contain remnants of old copies of database pages but it would be highly unlikely that there would ever be sufficient information lying around within these remnants to be able to perfectly reconstruct any of the prior states of the database and therefore be able to reconstruct the transaction log. HTH Hmm, this leads to the advanced question how much "history" could be revealed by experts (tech support / forensics)... Say, some malicious user (who has to have or gain DBA priviledge, I guess) would try to modify a database, delete/modify some important data, truncate/delete the current log (and would have to fiddle around with backups, too). How much of these actions could be detected - particulary some time after the modifications? (I don't expect a public answer here...)
(26 Jun '11, 16:30)
Volker Barth
Thanks, Mark, This explanation makes perfect sense to me! Thank you. I was pretty sure the answer was no, and now I understand why.
(27 Jun '11, 10:44)
Margaret Kam...
|
|
The short answer is no. Obviously, the application could have captured modifications to a table (or tables) and saved those previous row images in shadow tables, but I assume from your question those shadow tables don't exist. Let me ask a counter-question, Margaret: if there's no replication involved, why is the transaction log important? Or can you disclose the reason? No, there are no shadow tables in use. This client truncates their transaction log daily after backing up their database. They'd recently experienced a data loss that they had recovered from (this loss & recovery happened several weeks ago, I take it) and they are now looking at various back up & recovery options. So the question came up in the discussion. Your counter-question is a good one... I guess it isn't important.
(27 Jun '11, 10:40)
Margaret Kam...
|
Are you asking for an exact copy of a transaction log that once existed and now no longer exists? I.e. a log showing a history of changes. Or are you asking for a transaction log that contains all the data in the database? I think the answer is no for the former, and "run unload/reload" for the latter.
If it's a missing log necessary for SQL Remote, MobiLink or the Log Transfer Manager (rep server) the answer is definitely "no", don't even think about it :)
Looking for an exact copy of a transaction log that once existed and now no longer exists. I think the answer is probably "no" too. Fortunately, the database in question is not involved in any kind of replication.