Post reply

Who Truncated That Table & What Do I Do Now?

  • June 5, 2014 at 12:37 pm

    #1719401

    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]

  • June 6, 2014 at 7:56 am

    #1719677

    will this work on SQL 2005?

  • June 6, 2014 at 1:29 pm

    #1719818

    alen teplitsky (6/6/2014)

    will this work on SQL 2005?

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

    Sujeet Singh

  • June 9, 2014 at 6:35 am

    #1720058

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

  • February 20, 2015 at 6:44 am

    #1778930

    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!

  • February 20, 2015 at 7:06 am

    #1778940

    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.

  • February 20, 2015 at 8:43 am

    #1778969

    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.

  • February 20, 2015 at 2:33 pm

    #1779072

    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.

  • February 23, 2015 at 10:17 pm

    #1779477

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

  • February 25, 2015 at 1:52 am

    #1779816

    Great article and comments in this tread!

    Will try it later this week!

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

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