October 24, 2012 at 11:07 am
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
October 24, 2012 at 12:46 pm
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.
October 24, 2012 at 2:02 pm
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
October 24, 2012 at 2:33 pm
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 😉
+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.
October 24, 2012 at 2:41 pm
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.
October 25, 2012 at 1:29 am
Dave
"To my knowledge no vendor provides this, and I doubt any ever will. "
Burroughs/Unisys provided the "printaudit" program to do just that for their DMSII database , and Jade in New Zealand provides a similar tool for its OO database so your comment is incorrect.
As for the risk of Lawsuits - I'm sure that a corporate giant like Microsoft has a skilled enough legal team to draft a disclaimer that would enable them to doge that bullet
October 25, 2012 at 1:36 am
OK, OK, I admit it: there are probably situations where this would be useful. I suppose I was thinking more about the "delete without WHERE" option, which to me would likely be such a massive data mess that you'd be better off doing a point-in-time restore, rather than smaller mistakes that are perhaps more suited to this approach.
October 25, 2012 at 7:28 am
paul.knibbs (10/25/2012)
OK, OK, I admit it: there are probably situations where this would be useful. I suppose I was thinking more about the "delete without WHERE" option, which to me would likely be such a massive data mess that you'd be better off doing a point-in-time restore, rather than smaller mistakes that are perhaps more suited to this approach.
Even in those cases. At my last job we had a tech set all patient notes to contain the exact same data. The fix was to restore the database to another server, copy that table over, and then do an update. During that time inaccurate data was in the patient's chart. Granted, the users knew so they could adjust but that's still not a good thing. Due to the size of the database it would have been quicker to just roll back the transaction so we didn't have to take time restoring.
October 30, 2012 at 3:04 pm
Back in the days of SQL 2000, Lumigent Log Explorer came in handy on multiple occasions. Once it was a developer who had access to the database through a front end cold fusion page and performed a delete without a where clause. On another occasion, it was a DBA who accidentally ran only part of a query which left out both the BEGIN TRAN and the WHERE clause. In both cases, I was able to use Lumigent Log Explorer to restore the data by reversing the transactions. Restoring the databases to pull the data out would have taken significantly more time. It was also very handy at answering the "Whodunnit" questions.
I just recently had a situation in production where data was changed and we wanted to find out who made the change. A tool that could read the log would have been very handy to have.
November 3, 2012 at 5:24 pm
geoffrey.sturdy (10/25/2012)
Dave"To my knowledge no vendor provides this, and I doubt any ever will. "
Burroughs/Unisys provided the "printaudit" program to do just that for their DMSII database , and Jade in New Zealand provides a similar tool for its OO database so your comment is incorrect.
...
See also Oracle's LogMiner - built in by default.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply