locked
Interview Question :- You have a database using the Full Recovery model. Log backups are taken every 15 minutes from 9:00am. At 9:20am someone executes a TRUNCATE TABLE statement. Is it now possible to restore to a given point in time? RRS feed

  • Question

  • It appears to be a common misconception that TRUNCATE TABLE is not a logged operation. It is logged, it just doesn't log the individual row deletions. You can therefore roll back or restore to a point in time in either Full Recovery or Bulk Logged (assuming there are no minimally logged operations in the case of Bulk Logged).

    I couldn't find anything in BOL that explicitly states this, but you can infer it from from the entry on TRUNCATE TABLE. Also, I've referenced one of Paul Randall's articles from his excellent "A SQL Server DBA Myth a Day" series.


    Regards, Pradyothana
    Friday, October 7, 2011 7:49 AM

Answers

  • I suspect the purpose of the interview question is to flush out one's understanding that the TRUNCATE is actually mimimally-logged rather than non-logged one, thereby allowing PIT recovery in the FULL recovery model.  The bottom line is that you can always perform PIT recovery using database and transaction log backups in the FULL recovery model.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Stephanie Lv Friday, October 14, 2011 1:28 PM
    Saturday, October 8, 2011 3:01 AM
    Answerer

All replies

  • It's great that you have log backups, but you also need database backups.  There isn't enough information to answer the question.  When are the database backups completed?  If there are no database backups then you can't restore to any point.


    Eric Isaacs
    Friday, October 7, 2011 8:11 PM
  • I agree with Eric. First of all, you can take a full backup of your database. If you don't have a full backup, you have nothing.

    With a full database backup, you can restore it, and restore log backup for 9:00am, after, you can restore the next log backup to stop at the failure point, 9:20am.

     

    Regards


    Erickson Ricci
    Microsoft MCP, MCTS SQL Server 2005, 2008
    Visite o meu blog: http://ericksonricci.wordpress.com
    Me siga no twitter: @EricksonRicci
    LinkedIN: http://br.linkedin.com/in/ericksonricci
    e-mail: ericksonfabricio@gmail.com
    **Ajude a melhorar o sistema de busca do fórum.Marque a(s) resposta(s) que foram úteis**
    **Se esta resposta solucionou a questão, então, por favor, marque-a como resposta.**
    Friday, October 7, 2011 10:11 PM
  • I suspect the purpose of the interview question is to flush out one's understanding that the TRUNCATE is actually mimimally-logged rather than non-logged one, thereby allowing PIT recovery in the FULL recovery model.  The bottom line is that you can always perform PIT recovery using database and transaction log backups in the FULL recovery model.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Stephanie Lv Friday, October 14, 2011 1:28 PM
    Saturday, October 8, 2011 3:01 AM
    Answerer
  • Hi

    if there's a log backup, definitely there's a full backup. you can not backup log before database is not fully backed up. 

     

    Regards

    Ahmad

     

    Sunday, October 9, 2011 7:41 AM
  • Yes Possible,

    You Can Take Log Backup Say Around 9:25 AM

    Now You Have

    1. FullBackup
    2. Log Backup At 9:15
    3. and a Log Backup Taken At 9:25 After Truncate Issued.

     

    Restore Full Back With No-Recovery

    Restore Log with No-Recovery

    Restore Log Taken at 9:25 With STOPAT ='yourdate 9:19:59 AM'  Option with Recovery.

    You will get your data till 9:20 AM.

     

     

     

     

     


    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    Monday, October 10, 2011 5:08 AM