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
(BACKUP LOG [DB_NAME] TO DISK = ‘DRIVE:PATHDB_NAME_LOG.BAK’)
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
(BACKUP LOG [DB_NAME] TO DISK = ‘DRIVE:PATHDB_NAME_LOG.BAK’ WITH NO_TRUNCATE)
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.
5 Comments
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.
Good post. But I doubt you “go rouge” – you are ruddy-complected like me.
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.
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 ?
Did you delete the data file like my demo or did you drop the database?