Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332

    Unanswered: Restore from snapshot after changing recovery model

    Given the follwoing scenario: You create a snapshot of a database with full recovery model, change it's recovery model to simple, then perform several updates/modifications on the database, before you finally (due to an error) restore the database from the snapshot.

    Will the log chain be broken or not? The resteore sets the recovery model back to full, but I'm still a bit "curious" about the transaction logs.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    ummmm what logs or log backups are you talking about if you were in simple recovery? there is still a transaction log, but if I am not mistaken, in simple recovery the transaction is removed from the log after it is committed to the database.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by Thrasymachus
    ummmm what logs or log backups are you talking about if you were in simple recovery? there is still a transaction log, but if I am not mistaken, in simple recovery the transaction is removed from the log after it is committed to the database.
    Yes, but you restore (from the snapshot) back to a point in time where you have full recovery model. Thus, the transaction log should be intact, but I still want some confirmation that it really works.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what is it you are trying to do? you are not going to be able to recover to a point in time after you stopped taking t-log backups and switched your recovery to simple.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by Thrasymachus
    what is it you are trying to do? you are not going to be able to recover to a point in time after you stopped taking t-log backups and switched your recovery to simple.
    I'm trying to recover to a point in time which is BEFORE the change to simple recovery model, by restoring from a database snapshot. The restore itself works as intended, the recovery model changes back to full as expected, but I want to verify that the log also are reverted as one should expect.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You have shot yourself in the foot.

    When you changed the db to simple mode a checkpoint took place which marked as inactive any tranactions that would have been carried forward as active in a full recovery mode database.

    Your log will not restore because the lsn that the restored full recovery mode database needs to resynchronize has been marked inactive when the checkpoint took place as the database was changed to simple mode.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The log you have in the snapshot of the database is a snapshot of all transactions that occurred up UNTIL the snapshot. Nothing after that snapshot has been saved anywhere. The latest you can get that database back to is that snapshot.

  8. #8
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by MCrowley
    The log you have in the snapshot of the database is a snapshot of all transactions that occurred up UNTIL the snapshot. Nothing after that snapshot has been saved anywhere. The latest you can get that database back to is that snapshot.
    Well, that is exactly what I tried to figure out. So, if I restore the database from the snapshot, I will be able to work as if nothing happened, the logs are intact (as of snapshot time) and can be backed up as normal. If so, thanks... that's exactly what I wanted to know.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •