4

Does the SQL server restore the tail-log that backed up after the restoration operation, if I selected the option "Take Tail-Log backup before restore"?

Thanks In Advance

Improve this question

3 Answers 3

Reset to default
5

No, it does not restore the tail. The tail BEFORE the restore enables you to restore to the previous full backup and then play all transaction logs back and the last thing it will play back is the tail. This will get you back to the instant just before the bad restore.

Improve this answer
2
  • Then, who will play back the tail? the user, or the SQL?
    – T.Hajjar
    Oct 27, 2016 at 13:24
  • The tail log needs to be applied on first startup after Restore. Not sure if you manually must enable the recover mode. The restore command allows you to specify norecovery when you want to add additional logs. But I think if your backup includes the tail this is not needed. It’s only needed to add the live log (if you have it)
    – eckes
    Nov 22, 2017 at 9:54
3

The backup of the "log tail" prior to doing the restore simply ensures that it would be possible to restore the database to its state immediately prior to the restore.

The assumption is, when you're doing a restore, you're restoring the database to a state other than its current state. If you want the database in its current state, no restore is needed. It's assumed your restoring the database to some previous state.

Taking the "tail-log" backup before the restore means that, if you've got the latest full backup and all transaction log backups available, you can restore the database to the state it was prior to this restore, whether as the same database on the same server, or to a different name and/or a different server.

Improve this answer
3

One of the best practices when restoring over an existing database is to make sure that you can restore back to the moment before the restore. This can be for any number of reasons but the most frequent (in my experience) is someone is restoring over the wrong database, or maybe you forgot to grab the permissions from the old database, or that one table that you really really needed to keep the data from etc.

Ok, so you need to be able to restore right before the restore, in order to do that you need to have a full set of backups prior to that point. The designers of the tool have assumed that at this point you are taking regular full, differential and log backups and that you could, if you needed to recover to the last log backup taken. Basically the tail of the log backup is just a special name for one last log backup taken right before the restore. That final log backup is only needed IF you need to restore back to the point right before your restore.

End result, no, you don't actually need the tail of the log backup. Certainly not to do your current restore. It's just a really really good practice to take it, just in case something goes wrong. In all probability the file will sit out there until it's deleted by some cleanup process and never get used. But that one time you don't take one will be the one time you wish you had. (Based on my experience)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.