locked
simple recovery mode RRS feed

  • Question

  • Hi,

    I have a question..

    My production database is in full recovery mode and we have daily full backups and t-log backups after every 10 minutes.

    Suppose if I set the database in simple recovery mode for some reason, do the old full backups and t-log backups are of no use after setting the db in simple recovery mode. can I use these backups to go back in time.

    Regards





    • Edited by biltz Wednesday, September 24, 2014 7:26 AM
    Wednesday, September 24, 2014 7:25 AM

Answers

  • Hello,

    When you change the recovery model to simple you will be still able to do a Point-in-time restore with the old full + log backup.

    Only new log backup won't work.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 24, 2014 7:31 AM
  •  What will happen in following scenario:
      - He set simple recovery at 10 AM
      - 11 AM Database got crash 
      - He want to restore DB until 10:30 AM
      - There is no full backup after 10AM

    He loses. He will not be able to restore the database to any later point in time than 10:00.

    Also, see my first post for a chain of events where you can and cannot restore to point in time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, September 24, 2014 10:58 AM

All replies

  • Hello,

    When you change the recovery model to simple you will be still able to do a Point-in-time restore with the old full + log backup.

    Only new log backup won't work.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 24, 2014 7:31 AM
  • Assume the following chain of events:

    A: Full backup
    B: Log backup
    C: Switch to simple reocvery
    D: Full backup
    E: Log backup
    F: Switch back to full recovery.
    G: Log backup
    H: Full backup
    I: Log backup

    You will be able to restore to any point in time up to C. You will also be able to restore to any point in time from H and on - but only if the full backup at H is good. The log backups at point E and G will fail, since you cannot take log backups in simple recovery. And the database remains in simple recovery until you have taken a full backup.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, September 24, 2014 7:35 AM
  • Yes you can use your Old Full backup and T-log backups what is available before you changes it to Simple..

    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

    Wednesday, September 24, 2014 7:36 AM
  • Assume the following chain of events:

    A: Full backup
    B: Log backup
    C: Switch to simple reocvery
    D: Full backup
    E: Log backup
    F: Switch back to full recovery.
    G: Log backup
    H: Full backup
    I: Log backup

    You will be able to restore to any point in time up to C. You will also be able to restore to any point in time from H and on - but only if the full backup at H is good. The log backups at point E and G will fail, since you cannot take log backups in simple recovery. And the database remains in simple recovery until you have taken a full backup.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Erland,

    After you switch to simple recovery how is log backup E possible. Again how is log backup G possible after you switch to full recovery and dont take full backup  ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Wednesday, September 24, 2014 8:50 AM
  • Hello,

    When you change the recovery model to simple you will be still able to do a Point-in-time restore with the old full + log backup.

    Only new log backup won't work.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

     What will happen in following scenario:
      - He set simple recovery at 10 AM
      - 11 AM Database got crash 
      - He want to restore DB until 10:30 AM
      - There is no full backup after 10AM

    Wednesday, September 24, 2014 9:09 AM
  • After you switch to simple recovery how is log backup E possible. Again how is log backup G possible after you switch to full recovery and dont take full backup  ?

    Read my post again. :-) The log backups at point E and G will fail, since you cannot take log backups in simple recovery.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, September 24, 2014 10:56 AM
  •  What will happen in following scenario:
      - He set simple recovery at 10 AM
      - 11 AM Database got crash 
      - He want to restore DB until 10:30 AM
      - There is no full backup after 10AM

    He loses. He will not be able to restore the database to any later point in time than 10:00.

    Also, see my first post for a chain of events where you can and cannot restore to point in time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, September 24, 2014 10:58 AM