Who Truncated That Table & What Do I Do Now?

  • Robert Davis

    One Orange Chip

    Points: 28027

    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


    Points: 30014

    will this work on SQL 2005?

  • Divine Flame


    Points: 15941

    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: 3117

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

  • mauriciorpp

    Default port

    Points: 1472

    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

    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: 3090

    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.


  • Hommer

    Mr or Mrs. 500

    Points: 530

    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


    Points: 2829

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

  • gerard.degraaf 68780


    Points: 8

    Great article and comments in this tread!

    Will try it later this week!

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

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