"Take a tail-log backup"

Question

"Take a tail-log backup"

Beatrix Kiddo

SSC-Dedicated

Points: 32407
More actions
June 4, 2018 at 4:22 am

#354104

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 2

Thanks, John. Would you take a tail-log backup every 10 minutes (say)?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 4

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.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Beatrix Kiddo - Monday, June 4, 2018 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.

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 6

Thank you; I've fed that back to them, but felt I should check in case I was viewing it differently.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass