Necessity of backing up tail log when restoring transaction log? I have a question about whether or not it is necessary to back the tail log up when performing a restore of a database in Full Recovery mode. I've sprawled for a good while now, and have gotten conflicting reports.

Consider this scenario in SQL Server 2012:


Full database backup scheduled at 4:00 AM every morning.
Transaction log back ups every 15 minutes from 7:00 A.M. to 6:00 P.M.


With this backup plan in place, say we want to perform a deployment against this database after the final transaction log backup has kicked off at 6:00 P.M.. We then run various tests and fill the database with data that does not need to be retained after confirmation of a successful deployment.

The deployment is successful, all tests look to be in line, and we restore back to the 6:00 PM transaction log to rid of all the data just entered. We then run a final deployment against the database. At the time of this restore, is it necessary to back the tail log up if we have no need for all the data entered following the final transaction log?

I am receiving conflicting reports and dated posts/articles.

Could anyone outline the pros/cons of not performing the tail log backup?

Thanks!
At the time of this restore, is it necessary to back the tail log up if we have no need for all the data entered following the final transaction log?


No. If your transaction log backup ran at the top of the hour as expected, and you are certain you don't need any data later than that point, it is not necessary to take a tail of the log backup.