Full backup Scenario

  • sjimmo

    SSChampion

    Points: 11139

    Oleg,

    I wasn't arguing the point. I think it is a great question, causing a of of thought and good conversation. I just got it wrong, I admit it. My thinking was you needed the T-Log seperate. I also posted another location which explained the question.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • Oleg Netchaev

    SSCertifiable

    Points: 5268

    sjimmo (3/16/2010)


    Oleg,

    I wasn't arguing the point. I think it is a great question, causing a of of thought and good conversation. I just got it wrong, I admit it. My thinking was you needed the T-Log seperate. I also posted another location which explained the question.

    I know you were not Steve, and I apologize if my reply caused you to think so. I was just curious to actually check the backup behavior, so it is now tattooed in my brains.

    Oleg

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

    I will disagree with the official answer because the question did not ask if the transaction was included in the backup file but rather asked if the data would exist after restore.

    Because this is a full backup then the log must be restored as part of the complete database restore and the log has the transactions (no mention of point in time).

    From the manual:

    The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.

    So if the log was restored then all the data would be present.

    IMHO -- Mark D Powell --

  • Lynn Pettis

    SSC Guru

    Points: 442141

    Mark D Powell (3/16/2010)


    I will disagree with the official answer because the question did not ask if the transaction was included in the backup file but rather asked if the data would exist after restore.

    Because this is a full backup then the log must be restored as part of the complete database restore and the log has the transactions (no mention of point in time).

    From the manual:

    The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.

    So if the log was restored then all the data would be present.

    IMHO -- Mark D Powell --

    I'm confused. For the data to be present after a restore, it has to be in the backup, doesn't it?

    Could you explain what you are trying to say?

  • Cliff Jones

    SSChampion

    Points: 10517

    I think the link http://msdn.microsoft.com/en-us/library/ms186289.aspx explains it very succinctly that 'Full database backups represent the database at the time the backup finished.'

  • SQLRNNR

    SSC Guru

    Points: 281210

    Cliff Jones (3/16/2010)


    I think the link http://msdn.microsoft.com/en-us/library/ms186289.aspx explains it very succinctly that 'Full database backups represent the database at the time the backup finished.'

    Nice find.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

    Lynn, after a restore of a database in full backup mode the database reflects both the contents of the database and the transactions recovered from the log files since you must restore the logs as part of the restore when full backup mode is used.

    My argument is that the official answer is based on what is in the database backup file and not in the restored database which is what the question asked.

    Mark D Powell

  • Lynn Pettis

    SSC Guru

    Points: 442141

    Mark D Powell (3/16/2010)


    Lynn, after a restore of a database in full backup mode the database reflects both the contents of the database and the transactions recovered from the log files since you must restore the logs as part of the restore when full backup mode is used.

    My argument is that the official answer is based on what is in the database backup file and not in the restored database which is what the question asked.

    Mark D Powell

    But what is in the backup is what is restored from the backup. Any data not committed prior to the completion of the backup is still in the backup, just rolled back during the restore of the database.

  • john.arnott

    SSChampion

    Points: 11882

    Mark D Powell (3/16/2010)


    Lynn, after a restore of a database in full backup mode the database reflects both the contents of the database and the transactions recovered from the log files since you must restore the logs as part of the restore when full backup mode is used.

    My argument is that the official answer is based on what is in the database backup file and not in the restored database which is what the question asked.

    Mark D Powell

    Now I'm confused, too. Mark, you refer to the "official answer". Do you mean the one posted as the "Correct answer" and "Explanation" by Suja? Here they are for easy reference:

    Correct answer: It depends. If the insert statement gets completed before the backup process is over then it will contain all 10000 rows

    Explanation: Whenever a full backup is initiated it will place a pointer in the transaction log in order to understand from where the backup started. So any transaction happening,after the pointer, will be included in the backup. So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup. And if insert statement gets completed before the backup process then all 10000 rows will be present in the table after restoring this backup.

    The confusion I'm having is the apparent conflict between your statement that "... you must restore the logs..." and the idea that a "Full backup" includes any transaction logs necessary to recover the db to the point at which the backup completed. As Microsoft puts it:

    Using a Full Database Backup to Restore the Database

    You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. When the database is recovered, uncommitted transactions are rolled back. The restored database matches the state of the original database when the restored backup finished, minus any uncommitted transactions.

    See http://msdn.microsoft.com/en-us/library/ms186289.aspx (thanks to Cliff fo r this link).

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

    In which case the insert statement data would not be present unless it had completed prior to the backup ending.

    The key word in the question is restored. As I quoted a full recovery database restore must include the logs hence the data will be present.

  • Peter Trast

    SSCarpal Tunnel

    Points: 4332

    Since the question only wanted to know whether the 10,000 rows would be included in the backup, I would concur that the explanation is correct. We do not need a complete database restore, we are just looking to see if we would have the 10,000 inserted records. True, some data would be lost with no log restore, but that was not what the question was after.

    Is it possible I really understood the question??? 😉

    KILL 51

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • john.arnott

    SSChampion

    Points: 11882

    Ah. I think I see what you're getting at, Mark. The original explanation implies that as many of the 10000 rows as have been inserted by the end of the backup would then be included in a restored version of that database. That, of course, would not be true if they were part of a transaction that was committed after the backup finished, But wouldn't it still be true if the inserts were not designated within an explicit transaction? That is, if each insert were implicitly committed, then you could get some but not all of the 10,000 rows in your restored db.

    Or am I still missing something here?

  • vk-kirov

    SSCertifiable

    Points: 7686

    Mark D Powell (3/16/2010)


    Because this is a full backup ...

    From the manual:

    The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.

    So if the log was restored then all the data would be present.

    "Full backup" and "full recovery model" are two different things. You may do a full backup of a database that uses any of the three recovery models. A full backup always includes a part of the transaction log, no matter which recovery model is used. And that part of the transaction log will be processed during the restore process.

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    Well, I am admitedly being a bit picky with this, but the question never states which version of SQL Server it applies to. This behavior changed (I think it was between 7.0 and 2000).

    Do all versions since the change (SQL 2000, 2005,and 2008) all behave the same as well?

  • vk-kirov

    SSCertifiable

    Points: 7686

    Hugo Kornelis (3/16/2010)


    the backup first copies all the data pages, then adds all the log pages starting from the start of the oldest uncommitted transaction right up until the time the backup ends.

    I would like to add: "from the start of the oldest uncommitted transaction (which was in an uncommitted state when the backup started)", because without this remark one may think "the oldest uncommitted transaction which was in an uncommitted state when the backup finished to copy data pages" 🙂

    ChrisMoix-87856 (3/16/2010)


    This behavior changed (I think it was between 7.0 and 2000).

    It's very interesting. Could you please describe the previous behavior?

    ChrisMoix-87856 (3/16/2010)


    Do all versions since the change (SQL 2000, 2005,and 2008) all behave the same as well?

    Yes, they do.

Viewing 15 posts - 16 through 30 (of 66 total)

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