Backup of Tail-Log

Question

Backup of Tail-Log

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 28, 2016 at 7:31 am

#324278

I read some articles on backing up the tail log but I'm not clear on it.
When do you use this option and when do you not?
I seem to recall that it made the database unavailable when I selected it and I believe that I had to perform another transaction log backup with this option unchecked.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 11 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Yes, it makes the database unavailable when you use it. You would have needed to run a RESTORE, not another backup to get the DB usable again.

You use it when you want to take a log backup and ensure that no more transactions can be made to the DB after the backup finishes, for example when moving a DB to a different instance or server.

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

BrainDonor SSCoach Points: 19269 More actions · Answer 2

The main purpose is for restoring the data to the latest point in time possible as part of disaster recovery - if your existing log backups don't take you to the point-in-time that you need:

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

https://msdn.microsoft.com/en-GB/library/ms179314.aspx

Steve Hall
Linkedin
Blog Site

BrainDonor SSCoach Points: 19269 More actions · Answer 3

GilaMonster (1/28/2016)
You use it when you want to take a log backup and ensure that no more transactions can be made to the DB after the backup finishes, for example when moving a DB to a different instance or server.

I would never have thought of that use for it - I've always associated it purely with disaster recovery.

Steve Hall
Linkedin
Blog Site

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

BrainDonor (1/28/2016)
I would never have thought of that use for it - I've always associated it purely with disaster recovery.

There are two different things called the 'tail log backup'.

1) The one Paul talks about, WITH NO_TRUNCATE. That, if you run it on a normal, working database will do nothing to it. It's the equivalent of Copy_Only on a log backup.

2) The one that Welsh ran accidentally in the past, WITH NO_RECOVERY, which switches the database into a RESTORING state once it finishes, and is used when moving DBs around

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

GilaMonster (1/28/2016)
BrainDonor (1/28/2016)
I would never have thought of that use for it - I've always associated it purely with disaster recovery.
There are two different things called the 'tail log backup'.
1) The one Paul talks about, WITH NO_TRUNCATE. That, if you run it on a normal, working database will do nothing to it. It's the equivalent of Copy_Only on a log backup.
2) The one that Welsh ran accidentally in the past, WITH NO_RECOVERY, which switches the database into a RESTORING state once it finishes, and is used when moving DBs around

I do not remember what I did when I performed a Tail Log Backup to get the Database in a recovery state so that it is no longer in a restoring state.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Welsh Corgi (1/28/2016)
GilaMonster (1/28/2016)
BrainDonor (1/28/2016)
I would never have thought of that use for it - I've always associated it purely with disaster recovery.
There are two different things called the 'tail log backup'.
1) The one Paul talks about, WITH NO_TRUNCATE. That, if you run it on a normal, working database will do nothing to it. It's the equivalent of Copy_Only on a log backup.
2) The one that Welsh ran accidentally in the past, WITH NO_RECOVERY, which switches the database into a RESTORING state once it finishes, and is used when moving DBs around
I do not remember what I did when I performed a Tail Log Backup to get the Database in a recovery state so that it is no longer in a restoring state.

You ran RESTORE DATABASE <db name> WITH RECOVERY.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

GilaMonster (1/28/2016)
Welsh Corgi (1/28/2016)
GilaMonster (1/28/2016)
BrainDonor (1/28/2016)
I would never have thought of that use for it - I've always associated it purely with disaster recovery.
There are two different things called the 'tail log backup'.
1) The one Paul talks about, WITH NO_TRUNCATE. That, if you run it on a normal, working database will do nothing to it. It's the equivalent of Copy_Only on a log backup.
2) The one that Welsh ran accidentally in the past, WITH NO_RECOVERY, which switches the database into a RESTORING state once it finishes, and is used when moving DBs around
I do not remember what I did when I performed a Tail Log Backup to get the Database in a recovery state so that it is no longer in a restoring state.
You ran RESTORE DATABASE <db name> WITH RECOVERY.

That right.

Thanks Gail.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

BLOB EATER SSChampion Points: 14609 More actions · Answer 8

GilaMonster (1/28/2016)
BrainDonor (1/28/2016)
I would never have thought of that use for it - I've always associated it purely with disaster recovery.
There are two different things called the 'tail log backup'.
1) The one Paul talks about, WITH NO_TRUNCATE. That, if you run it on a normal, working database will do nothing to it. It's the equivalent of Copy_Only on a log backup.
2) The one that Welsh ran accidentally in the past, WITH NO_RECOVERY, which switches the database into a RESTORING state once it finishes, and is used when moving DBs around

I never knew about point 2. That's pretty cool.

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 9

GilaMonster (1/28/2016)
You use it when you want to take a log backup and ensure that no more transactions can be made to the DB after the backup finishes, for example when moving a DB to a different instance or server.

If memory serves, in a two-database Log Shipping configuration this is an early step in switching the Log Shipping Primary to the Log Shipping Secondary so you can later recover the Secondary and make it the new Primary.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Perry Whittle SSC Guru Points: 234064 More actions · Answer 10

Welsh Corgi (1/28/2016)
I read some articles on backing up the tail log but I'm not clear on it.
When do you use this option and when do you not?

Typically used in Log shipping when performing role reversal

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉