Who Truncated That Table & What Do I Do Now?

Question

Who Truncated That Table & What Do I Do Now?

Viewing 10 posts - 16 through 24 (of 24 total)

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

Thanks Kevin!

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

alen teplitsky

SSC-Dedicated

Points: 30014

June 6, 2014 at 7:56 am

#1719677

will this work on SQL 2005?

Divine Flame SSCoach Points: 15943 More actions · Answer 3

alen teplitsky (6/6/2014)
will this work on SQL 2005?

If you are asking about Point-in-time restores, then Yes.

Sujeet Singh

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 4

Thanks for a good and interesting article on a topic that rarely gets much attention!

mauriciorpp Default port Points: 1472 More actions · Answer 5

I was gonna talk about the logged/non-logged issue also, but that topic has been covered already.

so I came here to thank about the usage tips for those undocumented procs. it's a shame the engine is so powerfull yet MS hinders some details from us.

nice article!

Willem G SSC Eights! Points: 809 More actions · Answer 6

Thank you for a very useful article!

Small addition: make sure you choose the time specified in your FIRST 'restore log with stopat' command early enough, i.e. well before the problem you need to address.

This is because you can hop back and forth in time afterwards, changing the time in different 'restore log with stopat' commands , but these will not accept a time earlier than that used in the first 'stopat' command.

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 7

SQLRNNR (6/5/2014)
INCREDIBLEmouse (6/5/2014)
I see the "myth" is busted for Full model.
Is it still un-busted for the other models?
Truncate is still a logged operation in the other models.
The myth has been long busted around this one.

The confusion I think comes because many people don't realise that TRUNCATE is a DDL statement, not DML statement as DELETE is. (hence the need of ALTER TABLE permission)

That's why is fully logged at the level it happens (not row by row level) and can be rolled back all day long.

Cheers.

Hommer Mr or Mrs. 500 Points: 530 More actions · Answer 8

I recently had to fix a situation where half dozen of production tables got truncated. So I read your article with great interest.

However, after reading it, I realize we still need to face the cold reality, there is no easy way, except the third party tool option.

First of all, most of time, the truncate statement will not be run within a begin transaction, so rollback transaction as a easy way out is no use.

Point in time restore also often of time, is not practical. It has to take the database offline, and that downtime for sizable production database is not affordable.

So, the only option I took is to restore it to another server and merge the data back through linked servers, a painful red-eye overnight endeavor.

And, on top of it, I don't think any disaster recovery plan or High availability solution will help either.

Of cause the article itself is very well written.

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 9

Learned something new about functions fn_dblog () & fn_dump_dblog ()

gerard.degraaf 68780 Newbie Points: 8 More actions · Answer 10

Great article and comments in this tread!

Will try it later this week!