June 4, 2018 at 4:22 am
I'm testing a new backup technology and it has raised a question I thought I'd ask here. When you hear the words "take a tail-log backup" what do you automatically think?
1) take a normal transaction log backup.
2) take a transaction log backup and leave the database in the restoring state, unable to accept connections.
3) something else.
I'm not asking for the definition of a tail-log backup, I'm asking what you think when you read those words. For me it's 2. For a normal transaction log backup, I call that a transaction log backup, not a tail-log backup.
Thanks
June 4, 2018 at 4:30 am
I think it depends on the context, not on the method. If I'm backing up a log of a database with the intention of restoring over that database, I'd call that a tail-log backup regardless of what state I leave the database in.
John
June 4, 2018 at 6:14 am
Thanks, John. Would you take a tail-log backup every 10 minutes (say)?
June 4, 2018 at 6:48 am
No, absolutely not. Normal log backups in normal circumstances; a tail-log backup in extreme circumstances, for example someone has deleted a large table and I need to restore the whole database, or there's corruption in the data or log file that requires a restore. Of course, in those circumstances, it wouldn't hurt to make the database inaccessible immediately after the tail-log backup has been taken, but the point is that I'd still refer to the backup as tail-log whether I do or not. Hope that makes sense!
John
June 4, 2018 at 6:52 am
Great, thanks. That's what I think too. My take on this is that writing "scheduling tail-log backups" when it actually means "scheduling transaction log backups" is misleading.
June 5, 2018 at 5:45 am
Beatrix Kiddo - Monday, June 4, 2018 6:52 AMGreat, thanks. That's what I think too. My take on this is that writing "scheduling tail-log backups" when it actually means "scheduling transaction log backups" is misleading.
101% agreement. That is misleading. The tail log process is different than straight up log backups. You use the NORECOVERY command in a tail log scenario. That would be somewhat problematic for traditional "backup up the log every 10 minutes" to have your database go offline repeatedly. At least I think it would be a problem for most orgs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2018 at 7:43 am
Thank you; I've fed that back to them, but felt I should check in case I was viewing it differently.
June 5, 2018 at 12:57 pm
There's two possible things that come to mind.
1) A backup log with norecovery
2) A backup log with no_truncate
1st if I need to leave the DB in a restoring state, probably cause I'm moving it elsewhere
2nd if I have a suspect database that I'm about to restore over.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy