Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/313ddaf6-ec54-4a81-a104-7ffa9533‌​ca58.htm – jl. Oct 27 '10 at 19:10
@jl The webpage cannot be displayed Most likely cause: Some content or files on this webpage require a program that you don't have installed. What you can try: Search online for a program you can use to view this web content. Retype the address. Go back to the previous page. – l--''''''---------'''''''''''' Oct 27 '10 at 19:16
up vote 1 down vote accepted

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

share|improve this answer

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.

share|improve this answer
i thought all transactional logs are point in time? – l--''''''---------'''''''''''' Oct 27 '10 at 19:48
i dont understand the difference between this and if this were left unchecked – l--''''''---------'''''''''''' Oct 27 '10 at 19:52

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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