none
Backup tail-of-the-log after a restore?

    Question

  • I am studying for a SQL Server exam.  A couple of the practice test questions about recovery have had a restore command, THEN a BACKUP LOG command to capture the tail.  I do not understand why one would every not backup the tail first.  This particular question did involve restoring a read-only file instead of from a full backup, but I am not sure why that would change the order of operations.  Can anyone offer any insight into why backing up the tail would be the 2nd second step in a restore?
    Sunday, July 21, 2013 6:42 PM

Answers

  • Hi,

    The tail-log backup captures records on the transaction log that were written since the last transaction log backup. If you’re going to restore a database to the point of failure, then you need to take a tail-log backup before you start the restore operation.And restore sequence will be full backup(NR)-last diff backup(NR)--trn logbackup(NR)..tail log backup restore(WR)

    If you’re going to restore to a point in time prior to the last transaction log backup, if you’re moving the database from one server instance to another, or if you’re overwriting the existing database, then you won’t need a tail-log backup. If the transaction log is damaged and you can’t take a tail-log backup, then you must restore without one.

    I would like you to read this article and this.If still not clear revert

    NR:No rcovery

    WR:Wih recovery


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, July 22, 2013 5:02 AM
  • Having the tail backup as a second step only ever makes sense if your restore is running on a seperate box, in which case the sequence of backup and restore are Independent and it doesn't really matter what you do first... I have seen it once in a demo where the full restore actually took a longer while and was started first to save time...

    Monday, July 22, 2013 7:16 PM

All replies

  • Hi,

    The tail-log backup captures records on the transaction log that were written since the last transaction log backup. If you’re going to restore a database to the point of failure, then you need to take a tail-log backup before you start the restore operation.And restore sequence will be full backup(NR)-last diff backup(NR)--trn logbackup(NR)..tail log backup restore(WR)

    If you’re going to restore to a point in time prior to the last transaction log backup, if you’re moving the database from one server instance to another, or if you’re overwriting the existing database, then you won’t need a tail-log backup. If the transaction log is damaged and you can’t take a tail-log backup, then you must restore without one.

    I would like you to read this article and this.If still not clear revert

    NR:No rcovery

    WR:Wih recovery


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, July 22, 2013 5:02 AM
  • Shanky,

    My understanding of the restore process is exactly like you described above.  That is why I was so confused by the practice exam when the correct answer had the tail backup as the second step.  It seems to me that I have seen one other practice question that did that also and it makes no sense to me.  But seeing it twice motivated me to ask if there are any scenarios where the tail back up would be step number 2.  Your links are good ones.  I had already read them and others trying to come up with an answer.  Thanks for responding.

    • Edited by MrAndrewwayne Monday, July 22, 2013 2:14 PM After thought.
    Monday, July 22, 2013 2:11 PM
  • Having the tail backup as a second step only ever makes sense if your restore is running on a seperate box, in which case the sequence of backup and restore are Independent and it doesn't really matter what you do first... I have seen it once in a demo where the full restore actually took a longer while and was started first to save time...

    Monday, July 22, 2013 7:16 PM