SQL Server Performance Forum – Threads Archive

Translog restore after point in time

Last night I had the unpleasant experience of doing a point in time restore of a production database for the first time in my life. Unprepared as I was I searched all over the net to find a script to do the restore properly and finally I found it. The restore went fine, but one problem was left unresolved: There were some transactons beeing completed *after* the error was made, that I wanted to still be in the database after the restore. I ended up placing these manually by punching directly in EM and writing some adhoc tsql and I can tell you, it was a pain in the neck. Is there a way to restore portions of a translog *after* a point in time, or even better to manually delete the problem-entries in the translog and do a restore after that? And is it even possible to edit/read the translog? I posted a question on experts exchange last light also, where I adress this problem:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20796232.html Frett —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

The best option to read the TLog is to rely on third party tool like LOG EXPLORER from Lumigent Technologies. If you’re unsure or not tested this transactions to be applied even after point-in-time restore then rely on the tool to check thru. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

There are quite a few log explorer tools that may be able to do something like this. Many of these products allow you to step through a transaction log and rollback changes and things like that, so I would think they might allow you to only commit certain transactions also.

So are you saying satya what Negative implies here, that Log Explorer has the capabilities of only restoring certain transaction? —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

Yes, to be precise to tweak the Tlog and apply the transactions then you have to rely on third party tools. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Although the ‘best’ solution to read the log file is a third party tool, like satya suggested, you can use something like DBCC LOG (master,-1) replace master with your db. Option vary from -1 (most detailed) to 4 (least detailed). …since I don’t do my tax declaration in hexadecimal I suggest a third party tool. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] So consider this just for completeness. Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> "Real programmers don’t document, if it was hard to write it should be hard to understand" <hr height="1" noshade id="quote"></blockquote id="quote"> I really like this autosignature… Reminds me of some klingon programming style jokes. I’ll dig to find them. Really worth reading [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

Ok, thank you all…I have in the mean time here checked with<a target="_blank" href=http://www.lumigent.com>http://www.lumigent.com</a> and it seems like a really good product, allthough the price of $1400 is a little to stiff at the moment. But I’m sure that when the time comes the money has to be spent <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> Thanx alot! – Frettmaestro "Real programmers don’t document, if it was hard to write it should be hard to understand"

Also you can try similar tool from LockwoodTech. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>

Translog restore *after* point in time

Translog restore after point in time