SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A SQL Server Log Reader


A SQL Server Log Reader

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148082 Visits: 19444
paul.knibbs (10/24/2012)
Surely, for the faulty transaction to still be in the log file, the database must be in full (or bulk-logged) recovery, so you ought to be able to do a point in time restore to before the oops moment? In simple recovery the transaction could well have already been overwritten in the logs when you come to look at it, and if you're in full recovery and not taking regular log backups...well, I don't think I need to elaborate on the problems there!


Restoring isn't always an option. If I whack the current partition of orders for a 2TB database, I don't want to restore.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
bsclyde
bsclyde
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 409
+1 for including a log reader tool in SQL Server!
TravisDBA
TravisDBA
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3352 Visits: 3069
I use select * from fn_dblog (null,null) when i need to look into the T-Log. A little cumbersome at first, but has got some good information in it and if you know what you are looking for it works.:-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
djackson 22568
djackson 22568
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2646 Visits: 1241
Not going to happen. Microsoft knows first hand that there is this thing we call a lawyer, and that entity just loves to sue Microsoft for issues real and perceived. Develop a tool to read a log, wait for unqualified users to destroy their data, and companies are going to be filing lawsuit after lawsuit blaming Microsoft. I can imagine the testimony "well we let interns build it".

To my knowledge no vendor provides this, and I doubt any ever will.

Dave
IowaTechBear
IowaTechBear
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 111
djackson 22568 (10/24/2012)
Not going to happen. Microsoft knows first hand that there is this thing we call a lawyer, and that entity just loves to sue Microsoft for issues real and perceived. Develop a tool to read a log, wait for unqualified users to destroy their data, and companies are going to be filing lawsuit after lawsuit blaming Microsoft. I can imagine the testimony "well we let interns build it".

To my knowledge no vendor provides this, and I doubt any ever will.


I would love to believe that you are wrong, but it can be so true. Just look at the TV commercials looking for people who have experienced liver damage from taking too much acetaminophen (Tylenol) to use the advertiser's legal services for compensation. And then look at the decades of warnings that physicians, pharmacists and package labeling that warn about over user leading to liver damage. Yet people kept popping it as they do aspirin thinking that a couple of extra will not hurt.Crazy

I would love to see such a log tool, and I would hate to see the threat of lawsuits the reason why it would not be developed or released to the public.

-----------------
Larry
(What color is your database?)
Randy Rabin
Randy Rabin
SSChasing Mays
SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)

Group: General Forum Members
Points: 628 Visits: 561
IowaTechBear (10/24/2012)
Just look at the TV commercials

I'd like to think that the commercials are an indication that lawsuits are down and the lawyers are hurting for business, but then I take off my rosy glasses and don't believe it for a second.

Where we're running Enterprise (or Developer) Edition, something that helps me sleep at night is to have relatively-recent snapshots of ultra-critical databases. Yes, they create some I/O overhead and use up an unpredictable amount of disk space (so tread carefully before trying this) but they provide a really easy way to recover data back from a fat-finger or application "oops". Moreover you can have multiple snapshots, representing data at different points in time (last hour, yesterday, start of month, etc) It's not quite the same as single-transaction rollback, and not as point-in-time precise, but when they can be used for recovery they're so much faster than restoring from full backup.

@Scott A: +1 on the hockey, but at least you have some baseball



Jet-Ski
Jet-Ski
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 87
We actually had this just yesterday. Someone deleted 100 records from our CRM, told us about it 30 minutes later. We did a point in time restore, but lost 30 minutes of customer service cases and contact logs. It's less work adding those than it would have been to re-add the 100 contacts, but if we could have isolated just that transaction, it would have been nice.

Had they come to us the next day, they would have been SOL and would have had to re-add those contacts on their own.
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73005 Visits: 40959
Jet-Ski (10/24/2012)
We actually had this just yesterday. Someone deleted 100 records from our CRM, told us about it 30 minutes later. We did a point in time restore, but lost 30 minutes of customer service cases and contact logs. It's less work adding those than it would have been to re-add the 100 contacts, but if we could have isolated just that transaction, it would have been nice.

Had they come to us the next day, they would have been SOL and would have had to re-add those contacts on their own.


couldn't you have done a point in time restore on As a new database, and simply scripted the 100 deleted contacts out as a SQL INSERT statements to run on production?

then the 30 minutes of data and the rework would not have been needed at all;

if your database is huge, it might be hard to do, what with trying to come up with space for another monster db, but that's a better method, i think.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
AlreadyPicked
AlreadyPicked
Say Hey Kid
Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)

Group: General Forum Members
Points: 686 Visits: 329
Jet-Ski (10/24/2012)
We actually had this just yesterday. Someone deleted 100 records from our CRM, told us about it 30 minutes later. We did a point in time restore, but lost 30 minutes of customer service cases and contact logs. It's less work adding those than it would have been to re-add the 100 contacts, but if we could have isolated just that transaction, it would have been nice.

Had they come to us the next day, they would have been SOL and would have had to re-add those contacts on their own.


In those cases, we use Red Gate's SQL Data Compare. It's fantastic Wink

+1 for a log reader. A ex-Oracle dba here told me that Oracle's log is actually readable. No need for a tool.

___________________________________
I love you but you're standing on my foot.
Josh Turley-300197
Josh Turley-300197
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 104
We could have done that, in fact we've done just that in the past when they didn't tell us for a few days. The DB in question is larger than most of our DBs, but not obscene (30GB). Would have taken me more time to go through all that trouble of doing the restore, then scripting each affected table than it would for the collective people to just re-enter 30 minutes of data.

I've used SQL Data Compare before as well, nifty little tool. Works well in this situation, other than you still need to restore a dummy backup for it to compare against. I could be wrong, but that's what I remember from it.

I could use Red Gates virtual backup deal, but I haven't looked at that thoroughly. Like Steve said, this is a pretty rare occurrence.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search