Replaying SQL Server transaction logs on changed db schema
We have a big transaction log (1.3 GB) for a relatively simple SQL Server 2008 database (30 MB). It (log) contains all updates since first time when db was put into production and (now we see it) represents a valuable source of temporal data which would be interesting to us.
There would be some way to "replay" this entire log on a similar db (like the original, but with history tables and triggers added)?
This way we could reconstruct the same db, but with temporal data "extracted" from logs. This is a valuable knowledge that we overlooked the first time and should not rest on server log files.
UPDATE
I am NOT having any problems with "large" transaction logs. I DO NOT want to truncate the log. The temporal information which is contained in it is valuable (I sincerely expect that it would be clear now, since this is the THIRD time I repeat it).
To the "fastest gunners of west" out there, please do continue reading after "We have a big transaction log ..." above. I'm starting to think that actually was MY FAULT to begin a question with these words, since it appears that 80% of readers think that the question is about log truncation.
And to anyone who may wish to "suggest" another backup-and-log-truncation as a "solution" (BTW, completely missing the point), please read this.
Solution 1:
Transaction logs contain the binary deltas of modifications applied to a database since the last log truncation; the keyword here is "binary": they don't contain SQL queries or something similar, but are actually more akin to binary patches to be applied to a program.
For this reason, they can be replayed only on the exact physical database files they were originally linked to; replaying them on another database (even with the same schema) would be exactly like applying a patch to a different executable to the one it was written for: not possible at all.
You also can't replay them on the same database, if that gets modified; i.e., you can't restore the database from an old backup, bring it online, make any modification at all to it and then replay the logs against it; you actually lose any log-replaying capability as soon as you bring the database fully online (there's even a specific flag in SQL Server restore operations for that).
Solution 2:
Here they say:
Note that you can dump the contents of the current log file using an undocumented DBCC command:
DBCC LOG('<dbname>', [<option>])where
<option>is an integer between 1 and 4, which controls the level of detail of the information displayed by DBCC LOG.
If i do this with one of my db's I get a lot of information, but i think not enough to replay the log.
In other posts they mentioned these products which may solve your problems:
- http://www.iturnity.com/paginas/ie1018.htm
- http://www.apexsql.com/sql_tools_log.asp
Solution 3:
After re-reading your posting a few times.. I'd say you should look for some thirdparty log analysis tools.
If you have Sql Server 2000, the RedGate SQL Log Rescue tool is free. I haven't looked for other tools, but I'm sure there are some.