We help IT Professionals succeed at work.

What does tail-log backup restore provide?

rwheeler23
rwheeler23 asked
on
I went to restore some backups I was given and noticed a logbackup was being created for each restore. This is because the Tail-Log backup before restore checkbox is clicked. What exactly does this do? I see it says the database will be left in the restoring state? Will I be able access this database after restoring? Why would one choose this setting?
Comment
Watch Question

Muhammad Riaz ShahidDatabase Administrator

Commented:
This means you want to restore more Logs after the restore is finished. While the database is is in restoring state, you can not access it.
rwheeler23President

Author

Commented:
Then this is really odd. There are no more log files coming and when I restored the databases they are all on line and ready for use.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
The tail-log backup happens before the restore, to make sure you could recover the original db data you're restoring over up to the exact point in time you did the restore.  It's very likely you don't plan to ever restore the original db, so technically you don't need the tail-log backup.  But If you're doing regular log backups, the tail log backup won't take long at all, so you can just leave it on.  

Added background / details:
Say you run daily backups at 3AM and log backups every hour at 15 minutes after the hour.  So you have log backups from 3:15, 4:15 and 5:15.  Then at 5:47 you decide to restore over the db.  The tail-log backup saves the log data from 5:15 to 5:47 (i.e. after the last regular log backup) so that, if you needed to restore the original db later, you could recover all the way to 5:47 instead of only to 5:15.
rwheeler23President

Author

Commented:
Thanks both of you for your comments.