how to find the LSN for restoring to a marked Tran

Question

how to find the LSN for restoring to a marked Tran

winston Smith

SSCoach

Points: 19500
More actions
June 24, 2008 at 10:44 am

#193720

I have the transactionlog. theres one transaction that mess'es up tons of data. i want to restore to a transaction just before that. how do i see the transactions, the code that exectued the transaactions and the LSN so i can restore to a specific LSN??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

You can't really restore to a specific LSN in the manner you're thinking about. You can restore to a marked trans, but only if you mark the trans in code, which I doubt you've done.

Your only hope at this point, assuming you want to recover to before a specific transaction, is to either recover to a specific time (using STOPAT) or using a log parser like ApexSQL.

But there's no way to say, show me the LSNs for each transaction and recover to this specific LSN.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 2

I'm looking at the restore arguments.

http://msdn.microsoft.com/en-us/library/ms178615.aspx

Using STOPAT you can restore to a date and time.

Using STOPATMARK you can restore to a transaction mark or to a log sequence number.

The difficult part is to know the log sequence number. I think that you need a log reader tool for that.

Ola Hallengren

http://ola.hallengren.com

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

My mistake then... thanks for pointing that out olga... I've only used stopat for marks so I must've just skipped past that...

and yeah, he'll need a log parser for that.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

winston Smith SSCoach Points: 19500 More actions · Answer 4

cool. thanks guys. i was just playing with sql in a moment of bordom and then wondered how to find the LSN. you have answered my questions perfectly. i wonder why sql server doesnt include a log reader. does oracle or mySQL?