How to restore the tail end of a transaction log

Have you ever tried to restore a backup over an existing database and receive an error stating the tail of the log for the database has not been backed up? Many times if you are just restoring a database into a development environment you don’t care so you just restore WITH REPLACE and move on.  However if you want to ensure that your restore contains the latest transactions from the production database, simply make a transaction log backup.  Example


Now lets suppose you are working with a production system and something dreadful happens to the database.   Lets assume that you have a transactional database and take TLOG backups every 10 minutes. Now lets suppose at 2:00 in the afternoon a regular TLOG backup is taken and at 2:07 there is a hardware problem where your MDF file becomes corrupt. You now have to restore from backup. What happens to those 7 minutes of transactions?

Without recovering the tail end of the log, this data is gone. Since it was only the MDF file that was corrupt or lost, we can still take a log backup even without the MDF. To do so use


The key here is the NO_TRUNCATE statement.  Using NO_TRUNCATE allows you to be able to backup the transaction log without the database being online.  If your database is online you can just take a regular transaction log backup.

If you want to see this in action, check out my video.  In this video I go rouge and delete my MDF file and demonstrate how to backup and restore the tail end of the log.

How to backup and restore the tail end of a transaction log

This entry was posted in Backups/Recovery, Presentations, Query Tidbits, SQLServerPedia Syndication and tagged , , , , . Bookmark the permalink.

5 Responses to How to restore the tail end of a transaction log

  1. Having fallen into the part-time DBA role at my company, I appreciate articles like this: a single point, explained simply, with examples. This is how us self-teachers eat the elephant. Pick up an idea enough times, and eventually the light bulb starts glowing. Thanks for taking the time to touch on the subject.

  2. Dan Meenan says:

    Good post. But I doubt you “go rouge” – you are ruddy-complected like me.

  3. Brad says:

    Excellent post with video!!

    Knew the concept of Tail Log but was not aware how to go about it.

    This gave me clear instructions on doing tail log back up and using it for restore.

  4. Rishi says:

    hey , I went through the steps you have mentioned and after deleting the database and trying to backup the tail log but i get error saying

    Database ‘test’ doesnt exist , backuplog is terminating abnormally

    any inputs ?

Leave a Reply

Your email address will not be published. Required fields are marked *