2

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!

  • 2
    If there is no data that is required, a tail of the log is not required. If there is any data that wasn't captured in the 6:00 log run, then another log backup would be required but it doesn't need to be a tail of the log backup. – Sean Gallardy Jun 29 '16 at 20:35
  • 3
    What edition of sql server are you using ? If enterprise, you can use database snapshot to revert if successful deployment. If the database is small and you use compression, just take full backup and restore it with replace to overwrite existing one upon successful deployment. – Kin Shah Jun 29 '16 at 21:40
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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