sql server 2008 what does “back up the tail of the log..” do?

Question

thanks so several of you for answering my questions about backing up databases and logs. i am playing with a back up task, and there is a checkbox field for

back up the tail of the log, and leave the database in the restoring state.

what is the purpose of this? under which conditions should this be used?

Answer 1

You back up the tail of the log when you want the database to be unavailable for further transactions, like when you intend to restore the database in-place.

That is the key difference between a regular transaction log backup and a tail backup: when you back up the tail of the log, the database is put into the recovering state and no further transactions can be performed against it. A regular transaction log backup leaves the database in its operational state.

If you aren't going to restore the database in place, and want the database to remain available for further transactions, you just take a transaction log backup.

See Tail-Log Backups on MSDN

Answer 2

This would be useful if you want to do a point in time restore. Let's say a developer accidentally deletes a bunch of data from a table 15 minutes after your last transaction log backup. You woud backup up the tail end of the log. This would backup all of the transactions up to this last backup (including the developers mistake). If you know what time he made the mistake, you can restore the transactions right back to the point before he executed the query to delete all the records.

See the link below as well for more information.

http://msdn.microsoft.com/en-us/library/ms179314.aspx

Answer 3

The tail of the log is the transactions that exist between your last transaction log backup and now. @KenJ is correct on when you would use it.