Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Good understanding of With Recovery and With NORecovery

    Hi all I am just trying to get a good understading of Restoring. Now from what I have read from BOL With RECOVERY includes both the redo and undo phases and recovers the the database, additional backups cannnot be restored.


    WITH NO RECOVERY omits the undo phase to preserve uncommitted transactions. apparently this allows for restoring other backups to roll the database further forward in time.
    So one alows you to undo the other does not.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure what your question is.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    So one alows you to undo the other does not? And what exactly is meant by uncommited transactions??

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, you are not the one doing the undoing. That is all automatic.

    Imagine a database running along happily churning through transactions. If I power off the server, then SQL Server will necessarily come down. It will not bother to tell any of the processes what is going on. There is no time. In order to make sure that everything comes back up in a nice consistent way, SQL Server (and just about any other database management system) keeps a log of transactions or Transaction Log (catchy name, eh?). Transactions are written to this log in real time. There is not supposed to be any delay, since SQL server can not predict when the next power outage will be. In this log, transactions will be recorded along with their state at that point in time.

    Suppose you have an explicit transaction that deletes a rollup table, and then re-populates it with the results of a query that takes 3 hours to run. 2 hours into this re-population, I power off the server, so I can plug in the vacuum cleaner. The transaction log has recorded the delete of the table, and maybe even a partial result as an insert. when the power is restored later on, SQL Server goes through the redo phase, which reconstructs what was in memory at the time the power went out. Only transactions that are marked as committed will reconstructed. The Undo phase will look at the rollup table, and say the last time this table was "consistent" was before the delete (remember, the explicit transaction included the delete). As such, the Undo phase will remove any partial results that got populated into the rollup table, and then undo the delete, returning the rollup table to a last known good state.


    Does that help?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    >> vacuum cleaner

    ??????

    For goodness sake, at least make it a blender
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Smile

    Quote Originally Posted by MCrowley
    Well, you are not the one doing the undoing. That is all automatic.

    Imagine a database running along happily churning through transactions. If I power off the server, then SQL Server will necessarily come down. It will not bother to tell any of the processes what is going on. There is no time. In order to make sure that everything comes back up in a nice consistent way, SQL Server (and just about any other database management system) keeps a log of transactions or Transaction Log (catchy name, eh?). Transactions are written to this log in real time. There is not supposed to be any delay, since SQL server can not predict when the next power outage will be. In this log, transactions will be recorded along with their state at that point in time.

    Suppose you have an explicit transaction that deletes a rollup table, and then re-populates it with the results of a query that takes 3 hours to run. 2 hours into this re-population, I power off the server, so I can plug in the vacuum cleaner. The transaction log has recorded the delete of the table, and maybe even a partial result as an insert. when the power is restored later on, SQL Server goes through the redo phase, which reconstructs what was in memory at the time the power went out. Only transactions that are marked as committed will reconstructed. The Undo phase will look at the rollup table, and say the last time this table was "consistent" was before the delete (remember, the explicit transaction included the delete). As such, the Undo phase will remove any partial results that got populated into the rollup table, and then undo the delete, returning the rollup table to a last known good state.


    Does that help?

    McCrowley your the best thank you very much

Posting Permissions

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